I'm having trouble with VLOOKUP - not sure the values I'm providing are correct.

Copper Contributor

I have set lists of data and I need to compare them against each other to find matches. I thought it might be easier to compare if I concatenated the two sets of data - maybe not - I'm not sure. So basically this is what I have :

R-PROPR-RATE  E-PROPE-RATE  
AK006NSGV  AK006NSGV   AK006BASEAK006BASE#REF!
AL196NLK6  AL196NLK6   AK006BXMLDFAK006BXMLDF#N/A
AL212NLK6  AL212NLK6   AK006GNTAK006GNT#N/A
AL379NSGV  AL379NSGV   AK006NSGV  AK006NSGV  #N/A

In the third column there are about 10,000 values that I want to check against the 7th column, which has over 934,000 values. I know that at least some of the 10,000 values do exist  in the 7th column of data, but when I check the eight column, where the VLOOKUP is, in the row where I do know there is a match, I do not see the value from 7th column, it has the same "#N/A" value in it as all of column does.
This is the syntax I have for the formula:   =VLOOKUP(C2,G$2:G$934012,7,FALSE).
What have I got wrong in the VLOOKUP parameters?
Thanks!

6 Replies

@shaunullyett 

Try this:

=VLOOKUP(C2,G$2:G$934012,1,FALSE)

Thanks Patrick. I entered that syntax and copied it down beside the last column. Same results. No matches.
There could be an issue with G. Without seeing the workbook but going off experience, it's possible there are spaces before or after the IDs.

You might try:

=VLOOKUP("*"&C2&"*",G$2:G$934012,1,FALSE)

@Patrick2788That might have been it. I got a result in the first cell of the last column after jiggering it a bit. Not, to try and make that work in the huge data set. Thanks Patrick.
Can you tell me what the significance of changing that one parameter to 1 is?

 

I think I found the problem Patrick. The values in the column that was set as the lookup_value, had trailing spaces. I did a trim on that column, and now I am getting matches in the last column.
Thanks again for your help Patrick. Looking forward to heard the significance of changing that index parameter to 1.
Glad you were able to resolve it. 1 is the column because your table array is only 1 column G:G. It would be 7 if you were going from A:G.