Wrong returns in VLOOKUP

  • Thread starter Thread starter noah
  • Start date Start date
N

noah

I'm using a MATCH function within a VLOOKUP function it returns th
wrong value from the wrong column. What am I doing wrong?
This is my formula in R1C1:

=VLOOKUP(RC[-3],'[RATES.xls] Rat
Sheet'!R4C3:R500C114,MATCH(RC[-1],'[RATES.xls]Rat
Sheet'!R3C9:R3C114,0),FALSE)

My workbook looks up an account (RC[-3]) from the list on "Rates
workbook (R4C3:R500C3= Account Column) and matches the product on m
workbook (RC[-1]) from the product list (R3C9:R3C114= Product Row) o
"Rates" workbook and return the result within the field
(R4C9:R500C114).

(For example: I'm looking up Product "PRO2" for account "A100", wher
"A100" has a rate of "10" for "PRO2" and a rate of "20" for "PRO1".
should be getting the result from the column and row where "A100" meet
"PRO2" thus= "10" but instead am getting a result of "20".)

I think my problem may be that my Product list is made up with 2,3 an
4 character values. Although sorted in ascending order from left t
right. I have another workbook that looks up 2 character Products o
"Rates" workbook and works fine. But this workbook looks up 4 characte
Products.
( I use "Rates" workbook for look ups on several workbooks.)

Tell me if I lost you.
Any help or suggestions
 
Hi
try
=VLOOKUP(RC[-3],'[RATES.xls] Rate
Sheet'!R4C3:R500C114,MATCH(RC[-1],'[RATES.xls]Rate
Sheet'!R3C3:R3C114,0),FALSE)

You should start both function in the same column (C3)
 
The reason I first didn't start with the same column is that my product
don't start until column 9. Columns 4-8 are merged cells with othe
info for accounts.
Do you think it has anything to do with the 2-4 characters
 
Hi
no the reason is just that MATCH returns the column index in your
lookup range. But you use VLOOKUP with a different range. So this will
not work unleass you either make both ranges equal or subtract a value
from the MATCH return to reflect the different starting points
 
Thanks Frank.
I changed the column from 9 to 3 and it works. Even when the first
column headers have diffirent values than the sorted Products.

thanks again
 
Back
Top