Using a function as the lookup_value for MATCH

Question

I am trying to set up a formula for a two-way lookup that looks just like this, except that I would like to use the RIGHT function as the argument for the lookup_value of MATCH.
Long story short, the formula only works if I use a plain number as the lookup_value (or reference a cell that contains a plain number, or a formula that simply returns a plain number). Using LEFT (or a reference to a cell that contains just the LEFT function) makes MATCH fail.
A double lookup with INDEX+MATCH also shows the same problem.
I have tried to simulate the issue in this image. Can someone make sense of this behaviour, and hopefully point to any workarounds?

BradR · Answer

I believe the underlying issue is using RIGHT you receive a text answer, now numeric. The values you are looking up in your match are numbers. So in the screen cap below, you will see that if I add *1 to your formula you loose the n/a error.

Hopefully, this answers your questions.

Using a function as the lookup_value for MATCH

One Answer

Add your own answers!

Ask a Question