Name Lookup

[No Surprises]

I'm trying to accomplish what I would expect to be one of the simplest of tasks, but I'm coming up against persistent errors. I'm trying to use people's names to lookup corresponding data located on another sheet, but I can't seem to get it right. I've tried different variations of MATCH and VLOOKUP and they all either result in errors or misalligned results.

I'm attaching a very schematic example of what I'm trying to accomplish, so that hopefully somebody can tell me what I'm doing wrong. I'd like the rows in Table A to display the corresponding data for the named person from Table B.

NameLookup.xlsx

Please help, this is driving me insane. The solution must be ridiculously simple.

Thanks in advance.

[vlady]

Hello
can you try in
Column B
=INDEX(TableB!B2:B10,MATCH(A2,TableB!A2:A10))

in Column C
=INDEX(TableB!C2:C10,MATCH(A2,TableB!A2:A10))

[No Surprises]

Thanks so much! Your formula works like a charm in my example, but unfortunately I'm still getting alignment issues when I try to use it in my actual table. More hair-******* ahead I suppose.

[vlady]

Btw the ranges should have $ signs

like

=INDEX(TableB!$B$2:$B$10,MATCH(A2,TableB!$A$2:$A$10))

[No Surprises]

Unfortunately those aren't the issue.

Anybody have any idea what it is that causes the MATCH and VLOOKUP functions to return data from the wrong row when trying to look up a string? I mean, I have the exact same names listed in both tables, but whenever I try to use VLOOKUP or MATCH to pull the relevant information it keeps spitting out data from the wrong row. Could it be because it's seeing names that partially match and this is confusing it? I've tried to force it to look for exact matches, but whenever I do it gives me an #N/A error.

[vlady]

is it the given formula from your sample file..

it gives #na because the match is looking in the wrong column its looking in B and theres no index


eg.
=INDIRECT("Values!C"&MATCH(INDIRECT("A"&ROW()),TableB!$C$2:$C$8)) - looking the names in column C

here:
=INDEX(TableB!C2:C10,MATCH(INDIRECT("A"&ROW()),TableB!A2:A10))

[No Surprises]

I have a table much bigger than the one in my example that has hundreds of names, and when I use the formula you've given me (with the necessary adjustments) it returns data from the wrong row nearly every time. It's driving me crazy because there doesn't seem to be any way around it. If it were consistently off by the same number of rows, I guess I could just manually add a correction into the formula, but it's actually off by a different number every time and I don't know how to fix it.

[SuzieW]

A VLOOKUP will return an incorrect result if the data in the first column of the VLOOKUP table is not sorted into alphabetical/numerical order correctly. Could this be the problem?

[Cutter]

You haven't used the FALSE parameter in the MATCH() function.

vlady's formula worked in your sample because the data in the sample was sorted. I'm guessing that the data in your actual file is not sorted and that would cause the formula to yield incorrect results. You have to add the FALSE (or 0) to the VLOOKUP() or MATCH() to tell Excel to find an exact match, otherwise it looks for a 'close enough' match.

Use this in B2: =VLOOKUP($A2,TableB!$A$2:$C$10,2,0)
Use this in C2: =VLOOKUP($A2,TableB!$A$2:$C$10,3,0)

@SuzieW
Using FALSE as the search type overcomes that.

[Kevin UK]

As long as you use the exact match and use absolute references to your table array's, I see no problem using the VLOOKUP or INDEX functions.

[No Surprises]

I'm very appreciative of all the help guys. Unfortunately, telling VLOOKUP or MATCH to return exact matches in my actual table gives me an #N/A error every time. Also, the table is sorted alphabetically, so I don't think that's the issue.

[vlady]

can you try this one in any blank cell,,
type=then click the item to match=then click the correct answer in the table so it would look like this..

=A2=tableBA10

@ above the A2 is the word to find a match
tableBA10 is the correct position of the word found in A2

what's the outcome..

[Cutter]

The fact that you are getting #N/A returns for exact matches tells you that your data doesn't match. Look for leading/trailing spaces in either the value used for the search or in the values being searched.

[vlady]

That's my point if there are trailing/leading spaces..
@ no surprise if you done what is posted in #12 and received "false" then you have trailing/leading spaces.

probably use trim for the search and also check the data table.

[No Surprises]

There don't appear to be any trailing/leading spaces, but I went through this tutorial and realized the data types in the cells don't match. The problem is that when I use text to cells to convert the data as directed, the problem doesn't go away, and in fact it's still saying one column is type "1" and the other is type "2". What I'm doing is converting them both to general; if I try converting them to text, the values go crazy because they're derived from underlying formulas. I guess that's the key difference between my actual table and the example I posted; for the example I just manually entered a bunch of strings, but in my actual table the strings are the result of formulas. Is it just not possible to match strings if they're derived from formulas?

[No Surprises]

Now I have it saying they're both the same data type, but I'm still getting the #N/A error. This really sucks.