New Contributor

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

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-PROP R-RATE E-PROP E-RATE AK006 NSGV AK006NSGV AK006 BASE AK006BASE #REF! AL196 NLK6 AL196NLK6 AK006 BXMLDF AK006BXMLDF #N/A AL212 NLK6 AL212NLK6 AK006 GNT AK006GNT #N/A AL379 NSGV AL379NSGV AK006 NSGV 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

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

Try this:

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

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

Thanks Patrick. I entered that syntax and copied it down beside the last column. Same results. No matches.

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

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)

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

@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?

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

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.

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

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.