Excel Formula 2003/2007+ Compatability

cloud_dog

Hi

I've got a spreadsheet which basically acts an information / action guidance for a user and is primarily driven from a few drop-down lists of options.

I've developed this for 2007 onwards (xlsx) but (unfortunately) need to support 2003 (xls).

The problem I've hit is to do with nested logic in a formula and 2003 / xls will only support up to 7 nested statements.

Anyone got any idea how I could simply or revise the below statement so that it could work in 2003/xls spreadsheet?

H7 - Is a drop down list of options (referencing a list of Data column A
Data! Column B is the response / guidance information I want to display

=IF(H7=Data!$A$3,Data!$B$3,IF(H7=Data!$A$4,Data!$B$4,IF(H7=Data!$A$5,Data!$B$5,IF(H7=Data!$A$6,Data!$B$6,IF(H7=Data!$A$7,Data!$B$7,IF(H7=Data!$A$8,Data!$B$8,IF(H7=Data!$A$9,Data!$B$9,IF(H7=Data!$A$10,Data!$B$10,IF(H7=Data!$A$11,Data!$B$11,IF(H7=Data!$A$12,Data!$B$12,IF(H7=Data!$A$13,Data!$B$13,IF(H7=Data!$A$14,Data!$B$14,IF(H7=Data!$A$15,Data!$B$15,IF(H7=Data!$A$16,Data!$B$16,""))))))))))))))

At present there are 14 options.

Any Excel whizzes that can offer a better solution, i.e. is there a way of simply finding the H7 value in Column Data!A<range> and display Data!B<corresponding row value>?

I.e. H7 exists in A7 and then use the 7 to display the corresponding B7 value?

EDIT re above, obviously needs to be 2003/xls compliant

Le_Kirk

Without analysing your formula I would suggest a VLOOKUP or HLOOKUP table might help you with your table array as A3:16 and your offset column as B3:B16 and H7 as your lookup value. If that won't work you might have to consider splitting your nested IF statements.

bengalknights

You could always request the users on 2003 to install the compatibilty pack to allow it otherwise write it as a array or try and utilise index/match

cloud_dog

Thanks both, I'll give them a go

cloud_dog

Le_Kirk wrote: »

Without analysing your formula I would suggest a VLOOKUP or HLOOKUP table might help you with your table array as A3:A16 and your offset column as B3:B16 and H7 as your lookup value. If that won't work you might have to consider splitting your nested IF statements.

Tried:

VLOOKUP(H7,Data!A3:A16,Data!B3:B16,FALSE

I get #REF.

hawkeye9999

cloud_dog wrote: »

Tried:

VLOOKUP(H7,Data!A3:A16,Data!B3:B16,FALSE

I get #REF.

VLOOKUP(H7,Data!A3:B16,2,FALSE)

Le_Kirk

I don't know how that smiling angel got in there!!! When I gave the makeup of the formula I was just using your formula and assuming your data was where it was A3 - A16 etc. If you want to compare H7 to the data in A3 - B16 you need to do what Hawkeye posted and use "2" as the offset to select the data from B3 - B16 if H7 matches your data in A3 -A16.

cloud_dog

If only Excel could handle the smiling angel.

Ok, I tried your modified example and it does work. My only concern is that should the H7 text exist in the Data!B3:B16 range then I wouldn't want to find it (if that make sense).

Thanks for your help, it certainly makes the original formula much easier to use.

bod1467

=IF(ISNA(VLOOKUP(H7,Data!A3:B16,2,FALSE)),"What you want to show for NOT finding",VLOOKUP(H7,Data!A3:B16,2,FALSE))

Try that?

cloud_dog

I was wondering how to get around the 'N/A' default display.

Thanks