Multiple IF Statement with Vlookup not working.

[Superbob1980]

Good evening all. 

Having a real brain dead moment. Have used similar before but for some reason I am having some problems

This is my formula

=IF(C30="red",VLOOKUP($B$5,$A$38:$G$45,3,IF(C30="amber",VLOOKUP($B$5,$A$38:$G$45,4,IF(C30="green",VLOOKUP($B$5,$A$38:$G$45,5))))))

So all the time cell C30 is marked as RED it returns the value, but if it's Amber or Green it's returning False? 

I have used similar multiple times and don't recall having an issue - but for some reason this one is baffling me

Would appreciate any help

Thank you. 

[#2]

Hi @Superbob1980 ,

you are starting the next IF statement before the Vlookup formula is closed. You need to close the Vlookup brackets earlier. Also, you need to use the fourth parameter for Vlookup and set it to "False" for an exact match, unless your data is sorted and you want to return an approximate match. 

When using nested formulas, it can be helpful to use Alt+Enter to separate parts of the formula into different rows for better readability.

Here is what I THINK your formula should be corrected to:

=IF(C30="red",VLOOKUP($B$5,$A$38:$G$45,3,false),

IF(C30="amber",VLOOKUP($B$5,$A$38:$G$45,4,false),

IF(C30="green",VLOOKUP($B$5,$A$38:$G$45,5,false),"different color in C30"

)))

The same result can be achieved without IF statements by looking up the value in C30 in a list and returning the position of that result.

=VLOOKUP($B$5,$A$38:$G$45,MATCH(C30,{"red","amber","green"},0)+2,FALSE)

If C30 is red, then the MATCH function will return 1. Add 2 and arrive at the number 3, which is the column you want in the Vlookup.

[#3]

@Ingeborg Hawighorst Thank you so much that worked perfectly.