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

#### Fishboy

##### Well-known Member
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
CDEF
2Item Lookup ValueShortened ValueLookupsTest
317-8017=IF(D3<>"",LOOKUP(D3,Items!\$F\$2:\$G\$11,Items!\$H\$2:\$H\$11),"")17
4801-1,600801=IF(F4<>"",LOOKUP(F4,Items!\$F\$2:\$G\$11,Items!\$H\$2:\$H\$11),"")801
5801-1,600801Flavor text for 3801
61-161Flavor text for 31
781-16081Flavor text for 381
81-161Flavor text for 31
9801-1,600801Flavor text for 3801
10161-800161Flavor text for 3161
1181-16081Flavor text for 381
121-161Flavor text for 31
1317-8017Flavor text for 317
1417-8017Flavor text for 317
1581-16081Flavor text for 381
16801-1,600801Flavor text for 4801
17801-1,600801Flavor text for 6801

<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?

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### Mackers

##### Well-known Member
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

##### Well-known Member
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.

Replies
4
Views
328
Replies
1
Views
159
Replies
6
Views
207
Replies
6
Views
1K
Replies
5
Views
457

1,195,856
Messages
6,011,976
Members
441,661
Latest member
Pammie007

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back