Quantity formula when several options are selected.

**daffyw** · 03-05-2013, 04:25 AM

I have a spreadsheet attached which has thre pull down menus (lists), also in E2 is an output file, The formula I need is to fill a qauntity in E2 when seveal of the options are selected, for example when A1=380, A2=RED & A3 =E i want E2 to display 5.
another example, when A1=208, A2=BLUE & A3 =F I WANT E2 =10.

I can write the formula for two cells to = 5 but struggle to enter a code for all three, any suggestions please?

**benishiryo** · 03-05-2013, 04:40 AM

hi daffyw, do up a table of possible values & then do a VLOOKUP

**daffyw** · 03-05-2013, 04:44 AM

Thanks, Never thought of using vlookup...

**daffyw** · 03-05-2013, 06:25 AM

One other quick question, When options are selected that are unique, the display/ row shows #n/a, how do you hide or make the row blank instead of showing n/a?

**benishiryo** · 03-05-2013, 08:03 AM

in Excel 2003, try:
=IF(ISNA(VLOOKUP(A1&A2&A3,$K$1:$L$3,2,0)),"",VLOOKUP(A1&A2&A3,$K$1:$L$3,2,0))

in Excel 2007 & above:
=IFERROR(VLOOKUP(A1&A2&A3,$K$1:$L$3,2,0),"")