Replacing IF with CHOOSE

[Zagra147]

Ex. 2000
XP Pro SP3

Is there a way to replace nested IFs with the CHOOSE function so that I can overcome the seven nested IFs barrier in 2000?

I can replace IF with CHOOSE in a single IF formula which works, but I cannot see how to use 'nested' CHOOSE (if it is at all possible).



TIA

Zagra147

[royUK]

I think you need to give us an example of what you mean

[Zagra147]

This formula extracts data such as description, supplier, nutrition from an Excel database to compile daily food intake. A simple arbtrary code is input into A1 prefixed by the appropriate letter to access the correct sheet in the database.
IF(LEFT($A1,1)="A",VLOOKUP($A1,[Food_List_2011.xls]Alcohol!$B$1:$N$100,2,FALSE),IF(LEFT($A1,1)="F",VLOOKUP($A1,[Food_List_2011.xls]Foods!$B$1:$N$100,2,FALSE))) etc.

I wish to add more sheets, 'snacks', 'meals', etc. which would exceed the limit for nested IFs.

This works:
CHOOSE(LEFT($A1,1)="A",VLOOKUP($A1,[Food_List_2011.xls]Alcohol!$B$1:$N$100,2,FALSE)

But this doesn't:
CHOOSE(LEFT($A1,1)="A",VLOOKUP($A1,[Food_List_2011.xls]Alcohol!$B$1:$N$100,2,FALSE),CHOOSE(LEFT($A1,1)="F",VLOOKUP($A1,[Food_List_2011.xls]Foods!$B$1:$N$100,2,FALSE)))

[shg]

Perhaps ...

=VLOOKUP($A1, CHOOSE(MATCH(LEFT(A1), {"A","F","D"}, 0), Alcohol!$B$1:$N$100, Foods!$B$1:$N$100, Desserts!$B$1:$N$100), 2, FALSE)

[NBVC]

Something like this:

=VLOOKUP($A1,CHOOSE(MATCH(LEFT($A1,1),{"A","F"},0),[Food_List_2011.xls]Alcohol!$B$1:$N$100,[Food_List_2011.xls]Foods!$B$1:$N$100),2,FALSE)

listing all the possibilities in the second part of the Match function and the corresponding vlookup ranges as the choice items...

[Zagra147]

Thankyou shg & NVBC. That works fine with text strings - brilliant!
But, if I use your suggestions to access numerical data with a multiplier at the end of the formula to calculate quantities, e.g. (formula)*B1 - which is the portion size cell, then if 'A1' (the code cell) is blank, I get '#VALUE!' instead of a blank cell.
I have tried:
=IF(A1="","",(formula)*B1
with no success. Any suggestions?

Again

TIA

Zagra147

[NBVC]

Is that A1 the same A1 as in your Vlookup?

i.e

you have... =IF(A1="","",VLOOKUP(A1,.....)*B1) ?

If so, then it should return a blank if A1 is blank... and if the Vlookup returns a numerical result then you should get a numerical result.

Note that the multiplier needs to be associated only with the Vlookup() part.. so it should follow immediately after the closing parenthesis of the Vlookup() function and just before the final closing parenthis of the formula.

[Zagra147]

NBVC
That is correct.
This what I have:
=IF($A1="","",VLOOKUP($A1,CHOOSE(MATCH(LEFT($A1,1),{"A","F","M","R"},0),[Food_List_2011.xls]Alcohol!$D$9:$N$1150,[Food_List_2011.xls]Foods!$D$6:$O$1150,Food_List_2011.xls]Meals!$D$6:$O$1150,Food_List_2011.xls]RPSM!$D$6:$O$1150),4,FALSE)))*$B1/100.

That should work.

Ah. Just had a thought... It's a parenthesis problem:

=IF($A1="","",(VLOOKUP($A1,CHOOSE(MATCH(LEFT($A1,1),{"A","F","M","R"},0),[Food_List_2011.xls]Alcohol!$D$9:$N$1150,[Food_List_2011.xls]Foods!$D$6:$O$1150,[Food_List_2011.xls]Meals!$D$6:$O$1150,[Food_List_2011.xls]RPSM!$D$6:$O$1150),4,FALSE)))*$B1/100).

The whole vlookup including including the multiplier must be enclosed within ().

This now works ok.

Many thanks

Zagra147