FORMULA ASSISTANCE REQUIRED

[Megan]

I need the correct formula for the following problem I am experiencing.

If the reference number on sheet 1 column B matches the reference number in
column J on sheet 2 then transfer the data on sheet 2 column V.

Can anyone help please?

Kind regards
Megan

 

[Gary''s Student]

=IF(Sheet1!B1=Sheet2!J1,Sheet2!V1,"")
and copy to any desired row.

 

[Binyaaust]

try this:

=VLOOKUP(Sheet1!B:B,Sheet2!J:V,13,0)

 

[Megan]

I have tried this but it has brought back FALSE?

Any ideas?

 

[Megan]

I have tried this but it brings back NA instead of the info.

Any ideas?

 

[Ken]

Megan ...

Try ... =vlookup(WS2Range$J$1:$V$10,WS1$B2,13,0)

Adjust Range as nec & Copy down ... Kha

 

[Ken]

Megan ... Forget my other post ... I reversed the values:

Assuming you have headings in Row 1

Try ... =vlookup(WS1$B2,WS2$J$2:$V$10,13,0)

Adjust Range J2:V10 as necessary ... Then copy down ... Kha

 

[Megan]

It still isn't working and is bring back NA - can you look at my formula and
tell me if you spot a problem as I can't see one?

I thought that to do a VLOOKUP both worksheets should be laid out exactly
the same and be sorted alphabetically? My worksheets are very different and I
am unable to sort the data as it is very sensitive.

I have used =VLOOKUP('CASH RECVD'!$B6,'Contracts
Aug06-Date'!$A$7:$AK$479,13,0)

Also does the number 13 represent the column no which contains the data I am
trying to transfer?

And what does the 0 represent on the end?




Kind regards
Megan

 

[Abiodun]

Megan,

To use Vlookup, you require both sheets to have a common data e.g

Sheet 1

Column A Column U
Dog 12

Sheet 2

Column C Column L
Dog bravado

To Vlookup "Dog" is common to both sheets. This condition must be satisfied.
Secondly, selection of Rows & Columns on the sheet from which extraction is
to be made e.g sheet 2 must begin with Column C. Any selection before or
after C will fail vlookup.
Thirdly count from that selected "first cell" to the cell data resides i.e C
-L=10.

Your formula should look somewhat (vlookup(A1,sheet2!C1:L1,10,false)

wish you luck.

Regards,

Abiodun

 

[Ken]

Hi Megan ...

In your formula ... if your Range is A7:Ak479

(Cash Recvd) ... Cell B6 ... is what you are looking for
(Contracts Aug 06-Date) ... Range A7:AK479 is where you are looking for it
13 ... is 13th Col to Right in Range A7:AK479 (A=1, M=13) that you want
returned.
0 ... = False.

So if (Contracts Aug 06-Date) Col A contains the Value from B6 (Cash Recvd)
you would be returning value from Col M (Contracts Aug 06-Date)

Note: If value in Cell B6 repeats in your Search Range ... the 1st value
found in Col 13 will always be returned.

Above said ... from one of your previous Post:

WS1 ... Col B ... Contains what I am looking for
WS2 ... Col J ... is where this value is located
WS2 ... Col V ... is value I want returned when value in Col B is found in
Col J

in an empty cell enter ... =vlookup(WS1CellB?,WS2RangeJ?:V?,13,0)

If my search Range is Cols J?:V? ... then Col V is the 13th Col in the Range

This is how you can look for value in Cell B? in Col J & return value found
in Col V

Substitute ... WS Names, Cells & Ranges as necessary.

Kha