Fishboy
Well-known Member
- Joined
- Feb 13, 2015
- Messages
- 4,267
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
<tbody>
</tbody>
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?
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?