Lookup/Indew where reference value is a formula

[jessiegeorgia]

Hi All,

I have a MID formula that returns a two digit number I then want to use as a reference in a lookup. Referring to the cell containing the formula gives me a #N/A error, but if I overtype the formula with the value the lookup works, so I just need to know how to tell the lookup how to use the value rather than formula. The whole sheet needs to remain dynamic as is subject to updates so copying and pasting values isn't a working option,

Thanks in advance
Jess

 

[Marcelo Branco]

Be aware that MID returns a text not a number. So if the column where you are looking for the value contains numbers the formula fails and returns #N/A.
Try something like
=VLOOKUP (0+MID(...), table, column, 0)
0+ converts a number as text into a real number - for example: "10" to 10

Hope this helps

M.