How do I use VLOOKUP when the lookup_value has a comma in it?

0
votes

I have the following table...

enter image description here

I want to use VLOOKUP to fill in the "Manager Forward Name", in column D.

In English... I try to use the manager's reverse name, in column C, as a lookup_value to look up that string in column B, with the intent to return the value of that row from column 1 in the table_array range.

The formula looks like "=VLOOKUP(C2,A:B,1,0)"

My lookup_value is cell "C2" My table_array range "A:B" The col_index_num value is column "1" I want an exact match so I use a range_lookup "0"

I'm getting an "#N/A" value and I'm wondering if it's the comma in the lookup_value string.

I did try to make sure that columns A and B are, both, of type Text or, both, of type General but nothing seems to work.

Any ideas how to fix this?

PS: I tried to use other suggestions from other stackoverflow questions but nothing seems to work.

3
excelvlookup

3 Answers

1
votes

It is the fact that VLOOKUP expects the lookup value in the left most column and not the right.

Use INDEX/MATCH:

=INDEX(A:A,MATCH(C2,B:B,0))
0
votes

You are trying to find "Smith, Bob" in column A. That value is not present in column A. Perhaps you are confused by the presence of the value "Bob Smith", which to Excel is a completely different value.

0
votes

Write this formula in your D column -

=RIGHT(C2,LEN(C2)-SEARCH(" ",C2,1))&" "&LEFT(C2,SEARCH(",",C2,1)-1)

Thanks!