Vlookup on multiple sheets - #REF!

Question

I'm using a VLookup to find matches between two sheets, IDMS and MLD. Then I want to post the info from Column Z on MLD in the cell. The following formula works only when the column index number is 1, but changes to #REF! with any other number (even though there is data, no formulas, in several columns after that):

=VLOOKUP(IDMS!C1995,MLD!$A$1665:$A$2988,1,FALSE)

I've been looking for a couple hours for a solution and can't find one. Ideas? Please and thank you!!

Answer 1

Thank you! Expanding the range was the missing piece.

Answer 2

First of all, the only way I know of that you can get a #REF! is if the tab names are not as represented in your formula. So please check again for leading and/or trailing spaces or other characters.

Second, if you are looking up a value associated with the value in column A of MLD, then you need to include more columns in the look up range and specify the column containing the desired value. For example, if the desired value is in column C then:

=VLOOKUP(IDMS!C1995,MLD!$A$1665:$C$2988,3,FALSE)

Kevin

Answer 3

Yes, the tabs are correct. And while I do want to determine whether the value (which is an ID #) in IDMS is also in MLD, I need the corresponding dollar amount from the MLD sheet for each ID # that is in both sheets.

Answer 4

Also, since you are only looking up in one column, I suspect you are only trying to determine whether or not the value in IDMS!C1995 is in the list MLD!$A$1665:$A$2988. This formula is more specific to that end:

=MATCH(IDMS!C1995,MLD!$A$1665:$A$2988,0)

Kevin

Answer 5

Are you sure the tab names are exactly "IDMS" and "MDL"? No spaces or other characters?

Kevin