VLOOKUP not working

**booo** · 10-06-2010, 04:16 PM

I have attached a file which has two spreadsheets. I have data in 'Sheet1', which needs to be lookedup in 'Sheet2'. The field 'Rate' in sheet 1 has be filled up by looking up values of the field 'Cur.Value' in sheet 2.

I wrote a VLOOKUP function which gives me '#REF' error. Kindly help. I have attached the file with the VLOOKUP function associated.

Thanks.

**ChemistB** · 10-06-2010, 04:25 PM

The column returned is relative to the columns of your range. Since your range is D2:E15, Column 1 is D and 2 is E, you want to return column 2 so modify your formula as follows;

=VLOOKUP(F2,Sheet2!D2:E15,2,FALSE)

Also, you want your range to remain constant when you drag your formula so lock the cell references in place with $ like so
=VLOOKUP(F2,Sheet2!$D$2:$E$15,2,FALSE)
You'll get #N/A when there is no match so I'd also add an IF statement like so
IF(ISERROR(VLOOKUP(F2,Sheet2!$D$2:$E$15,2,FALSE)),"No Match", VLOOKUP(F2,Sheet2!$D$2:$E$15,2,FALSE))
Hope that helps.

**booo** · 10-06-2010, 05:14 PM

awesome.

Thank you so much. That works.

Now that I have the pivot working properly, is it possible that instead of displaying the corresponding value, I could get it to mutiply the corresponding value with another value and display just the result?

**ChemistB** · 10-07-2010, 09:33 AM

Are we talking about a different issue (a pivot table)? or the VLOOKUP. With the VLOOKUP, you can multiply the result right within your formula.

=VLOOKUP(F2,Sheet2!$D$2:$E$15,2,FALSE)*0.6
or
=VLOOKUP(F2,Sheet2!$D$2:$E$15,2,FALSE) * F2 (cell will change when formula dragged)
or
=VLOOKUP(F2,Sheet2!$D$2:$E$15,2,FALSE) * $F$2 (cell is constant)
Is that what you are looking for?