Lookup provides N/A when referencing the result of a formula

[Fishboy]

Hi all,

This one is really bugging me. In an Excel 2010 workbook I have various tables and I am trying to use one to lookup values from the other. The table I am writing my lookups into has some columns which are populated by formulas and it is these that are giving me my problem.

Excel 2010

	C	D	E	F
2	Item Lookup Value	Shortened Value	Lookups	Test
3	17-80	17	=IF(D3<>"",LOOKUP(D3,Items!$F$2:$G$11,Items!$H$2:$H$11),"")	17
4	801-1,600	801	=IF(F4<>"",LOOKUP(F4,Items!$F$2:$G$11,Items!$H$2:$H$11),"")	801
5	801-1,600	801	Flavor text for 3	801
6	1-16	1	Flavor text for 3	1
7	81-160	81	Flavor text for 3	81
8	1-16	1	Flavor text for 3	1
9	801-1,600	801	Flavor text for 3	801
10	161-800	161	Flavor text for 3	161
11	81-160	81	Flavor text for 3	81
12	1-16	1	Flavor text for 3	1
13	17-80	17	Flavor text for 3	17
14	17-80	17	Flavor text for 3	17
15	81-160	81	Flavor text for 3	81
16	801-1,600	801	Flavor text for 4	801
17	801-1,600	801	Flavor text for 6	801

<tbody>
</tbody>

Gem Calculations​

In my example above, the values I want to look up are in column C, but as they are hyphenated and the lookup reference is not I cannot do a like for like lookup.

As a workaround in column D I used this formula to truncate the values down to the numbers before the hyphen:
=IF(C3<>"",LEFT(C3,FIND("-",C3&"-")-1),"")

Column E is where I am entering my lookup formulas, and they look like this:
=IF(D3<>"",LOOKUP(D3,Items!$F$2:$G$11,Items!$H$2:$H$11),"")

In my example the lookup in E3 is referencing the truncated value from D3, but this returns #N/A!

Next I tried the same thing in E4, but instead referenced a manually entered value in F4. This works fine. The Test column F was me proving to myself that if I manually enter the values from D into F, the lookups all worked.

So in a nutshell the lookups work if the lookup value is manually entered, but not if the lookup value is the result of a formula.

My next step was to try using INDIRECT:
=IF(D3<>"",LOOKUP(INDIRECT(D3),Items!$F$2:$G$11,Items!$H$2:$H$11),"")
This also returns #N/A!

I have checked and double-checked that the format of the cells are set to number, but this also had no effect. I cannot get my head around why the lookups work one way but not the other, when technically speaking the value I am trying to lookup is the same with both methods.

Does anyone have any suggestions?

 

[Mackers]

Hi Fishboy

The first thing that occurs is that the LEFT() function returns a string rather than a number, so you have to put VALUE() around it, or 0+LEFT() or --LEFT(). For example, if you take the LEFT() of your "17-80" string, you would return {"17"} rather than {17}. If you take --LEFT() of it you would return {17} and be able to match appropriately.

Edit: to be clear, changing the format of the cell in the format dropdown will *not* change a "17" string to a 17 number, you need to manipulate the text into a number using one of the various ways such as N() function, VALUE() function, -- or 0+ as shown above.

Hope that helps

Mackers

 

[Fishboy]

Hi Fishboy

The first thing that occurs is that the LEFT() function returns a string rather than a number, so you have to put VALUE() around it, or 0+LEFT() or --LEFT(). For example, if you take the LEFT() of your "17-80" string, you would return {"17"} rather than {17}. If you take --LEFT() of it you would return {17} and be able to match appropriately.

Edit: to be clear, changing the format of the cell in the format dropdown will *not* change a "17" string to a 17 number, you need to manipulate the text into a number using one of the various ways such as N() function, VALUE() function, -- or 0+ as shown above.

Hope that helps

Mackers

Hi Mackers,

That was it! I added -- to the front of my LEFT() statements and magically everything started working!

Thanks mate.