VLOOKUP Not matching with Cell Reference but with String

petef

I am doing a cross sheet VLOOKUP using this formula: =VLOOKUP(Region@row, {GC Consulting Approver}, 2, false). This returns a No Match error even though there is a match.

If I use this version of the formula, =VLOOKUP("Americas Latin America​", {GC Consulting Approver}, 2, false), it makes the match.

The search value is in a Number/Text column. The 1st column of the lookup range is a Primary Column and is also Number/Text. The value I am trying to lookup is in a Contact column and my formula is also in a Contact column.

I want to use the Region@row method since I will be adding rows to this sheet. Any help would be appreciated.

petef

@Erika Sajdak thanks. That did not fix it, but it looks like I was not specifying the correct column for the INDEX function. This is the working formula: =INDEX({GC Consulting Approvers}, MATCH(Region@row, {GC Consulting Approvers}, 1), 2).

Erika Sajdak

Is the data in Region@row populated by a formula or link? Sometimes I have had lookups fail because it is not a Value in the cell.

DKazatsky2

Hi @petef,

Try using an INDEX/MATCH formula instead.

=INDEX({GC Consulting Approver}, MATCH(Region@row, {Region}, 0))

Hope this helps.

Dave

petef

@Erika Sajdak The data is just text. No formula was used to generate it.

@DKazatsky2 Your formula does not work either. If I replace Region@row with the text string I am searching for it will make the match, otherwise it returns No Match. It appears to me that the issue is related to data types. The source of the search data is a text/number column. The column that is being searched for the match is also a text/number column, but it is also the primary column for the approver sheet. Is there a way to force the Region@row to be text?

Erika Sajdak

Yes! You can match to TEXT(Region@row), perhaps?

DKazatsky2

@petef

It would be helpful if could provide screenshots of the 2 values so we can see if there is a data discrepancy. Make sure to block any sensitive data.

petef

@Erika Sajdak thanks. That did not fix it, but it looks like I was not specifying the correct column for the INDEX function. This is the working formula: =INDEX({GC Consulting Approvers}, MATCH(Region@row, {GC Consulting Approvers}, 1), 2).