Index/Match from NestedIf

[lol.xls]

Can I use Index/Match to correlate a value given from a nested if formula? For example.

AW15 contains:

=IF(AG15="TRUE",AH15,IF(AND(AK15="",AL15="",AM15="",AN15="",AO15=""),AJ15,IF(AND(AL15="",AM15="",AN15="",AO15=""),AK15,IF(AND(AM15="",AN15="",AO15=""),AL15,IF(AND(AN15="",AO15=""),AM15,IF(AO15="",AN15,AO15))))))

AX15 contains:

=INDEX(RANGES!D2:D254,MATCH(AW11,RANGES!C2:C254,0))

AW15's formula returns a code "10624" Which should make AX15's value "8.30".

If I overwrite AW15 and manually enter 10624, then the index match function's properly, however, I need the nested if to sort the correct data. Any help would be appreciated.

 

[puru.sve]

check the data ..might be it in the text ... so when u type 10624 then it considered as number.

 

[lol.xls]

Formatting is good. Just to be certain, if I manually type 10624 into AW14 and change the reference in the Index/Match formula to that cell, it calls the correct value. If I use format painter on AW14, and apply it to AW15, I still get an "#N/A" IN AX15. I'm curious if this is because AW15 is a formula and not a value. Should I just use some code to copy this range, and paste as value elsewhere in the sheet and calculate from there? That seems like some unnecessary steps. Any more thoughts?

 

[lol.xls]

Can anyone offer any help on this one? Here is a link to the file: https://dl.dropboxusercontent.com/u/19192249/New TS.xlsm

 

[ukmikeb]

Hi

Try coercing the Code to a number using "+0".

Formulas :

=INDEX(RANGES!$D$2:$D$254,MATCH(AW15+0,RANGES!$C$2:$C$254,0))

and
=VLOOKUP(AW15+0,dataset,2,FALSE)

and I've added FALSE to the VLOOKUP in case the Codes aren't in numerical sequence.

hth

 

[lol.xls]

Interesting. I've never seen that used before. If you have time, I'm curious to know exactly why that worked. Thank you very much!!!

 

[ukmikeb]

Hi

Actually the root of the problem is the way Excel has interpreted the nested IF. It assumes because those cells are tested as strings that they must be strings and that is borne out by the fact that the resultant value 10624 being positioned on the left of the cell, AJ15.

However, there is another problem with the formula! You are testing a text value "TRUE" against a Boolean result TRUE so that test will never be satisfied.

This is the corrected formula :-

=IF(AG15,AH15,IF(AND(AK15="",AL15="",AM15="",AN15="",AO15=""),AJ15,IF(AND(AL15="",AM15="",AN15="",AO15=""),AK15,IF(AND(AM15="",AN15="",AO15=""),AL15,IF(AND(AN15="",AO15=""),AM15,IF(AO15="",AN15,AO15))))))

nb you don't need to specify TRUE.

If the columns are filled sequentially and you DON'T have intervening blanks then the formula could be simplified to :-

=IF(AG15,AH15,INDEX(AJ15:AO15,,CHOOSE(COUNTIF(AK15:AO15,"")+1,6,5,4,3,2,1)))

hth