Using a function as the lookup_value for MATCH

Super User Asked by Francesco on November 28, 2020

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?

One 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.

enter image description here

Hopefully, this answers your questions.

Answered by BradR on November 28, 2020

Add your own answers!

Related Questions

Working in multiple languages/locales

2  Asked on November 3, 2021 by federico-nafria


Issues with UEFI: Can’t save settings and can’t boot OS automatically

2  Asked on November 3, 2021 by devank-mishra


Thinkpad X30 middle-click

4  Asked on November 3, 2021 by ian93


windows 10: Weird mouse movement (jumping and circle seen)

2  Asked on November 3, 2021 by user1480864


How to disable WSL password?

1  Asked on November 3, 2021


What to do when I need more keys than my keyboard has?

13  Asked on November 3, 2021 by carlanthony-zasowski


Ask a Question

Get help from others!

© 2022 All rights reserved. Sites we Love: PCI Database, MenuIva, UKBizDB, Menu Kuliner, Sharing RPP, SolveDir