Excel 2007 : Alternative to if statement ?

**tommo49** · 11-10-2009, 09:21 AM

I need a formula that gives the following result

If I type a colour into the first column, a price appears in the second column, depending on the colour I type in.

I have got this working up to a point using the if statement below but have exceeded nesting levels.

=IF(ISNUMBER(SEARCH("orange",A2)),38.36,IF(ISNUMBER(SEARCH("black",A2)),23.38,IF(ISNUMBER(SEARCH("blue",A2)),23.21,IF(ISNUMBER(SEARCH("white",A2)),31.5,IF(ISNUMBER(SEARCH("brown",A2)),36.28,IF(ISNUMBER(SEARCH("green",A2)),26.89))))))

Any help much appreciated.

Tommo

**Andy Pope** · 11-10-2009, 09:27 AM

Try using a VLOOKUP instead.

You will need a table of colours and values.

Edit: hold that thought.

Does A2 contain other information besides colour?

**DonkeyOte** · 11-10-2009, 09:29 AM

I would suggest you list your colours in a column somewhere with the respective value in the adjacent cells.... order in terms of colour preference (first to be looked for listed first) ... let's assume your list is in AA1:AB10 then something like:

=INDEX(AB1:AB10,MATCH(TRUE,INDEX(ISNUMBER(SEARCH(AA1:AA10,A2)),0),0))

**tommo49** · 11-10-2009, 10:16 AM

Thanks for the reply but that is not quite what I am looking for.

I need to input the colour manually and have the price go in automatically.

ie if I type orange in column A 20.00 appears in column B

blue = 30.00
brown = 40.00
green = 50.00

etc

Tommo

Edit: To Andy Pope. No, the only information in the A column is the colour

**Andy Pope** · 11-10-2009, 10:26 AM

=VLOOKUP(A1,{"Blue",30;"Brown",40;"Green",50},2,FALSE)

Although as DO and I said it would be better to create a table of colours and values.
So if the table was in D1:E3 the formula would be.

=VLOOKUP(A1,$D$1:$E$3,2,FALSE)