If then, vlook up and list box

[Kathleen]

Hi,

A1 is a list box with 12 options to pick from
B1 is a yes or no field
C1 needs to look at B1, if yes, look at A1, see the chosen option and go to
a separate worksheet to pull the corresponding cost. If no, then enter $0.00.

I've playing with this formula but not getting anywhere. Is it possible to
do this
=IF(ISNA(VLOOKUP(GB3,RATES!$A$67:$B$78,2,FALSE)),"",VLOOKUP(GB3,RATES!$A$67:$B$79,2,FALSE))

Kathleen

 

[Dave Peterson]

=if(b1="no",0,vlookup(a1,rates!a:b,2,false))

This assumes that the value in A1 is either Yes or No (nothing else!).

You may want to add that =isna() stuff in case there's no match.

=IF(B1="no",0,IF(ISNA(VLOOKUP(A1,Rates!A:B,2,FALSE)),"",
VLOOKUP(A1,Rates!A:B,2,FALSE)))

 

[Kathleen]

Hi Dave,

I think I've done a poor job in explaining and I apologize. I think I
should have added another cell to make more sense.

A1 is a list box of 12 different coverage types
B1 is a yes or no field

D1/E1 (on separate rate sheet) is a listing of rates corresponding to the
coverage types in A1 ie A1 = car, rate sheet = car (D1) $100 (E1)

C1 needs to look at B1, if yes, then look at A1 - see coverage type - then
go to rate sheet to get corresponding cost.

Outcome examples:

A1 available options in list box = car, cat, dog, truck
Rate Sheet columns D1/E1
car $100
cat $ 50
dog $ 75
Truck $200

A1 = car
B1 = yes
C1 = $100 (from rate sheet)

......
A2 = car
B2 = no
C2 = $0.00

-----

A3 = dog
B3 = yes
C3 = $75 (from rate sheet)

Does this make sense?

Either way, I very much appreciate your taking the time to help.

 

[E.Q.]

Hi Kathleen -
I created a formula that might be along the lines of what your after.
I used your example and created a sheet named "Rates" and populated d1:e4 as
you implied. On the original sheet, I placed a value of True or False in
cell B1 and typed car or cat or whatever. (I'm assuming your list box
assigns such a value to cell a1) Then this formula

=IF(B1,VLOOKUP(A1,rates!$D$1:$E$4,2,FALSE),0)

If cell b1 contained the word "Yes" or some other text, then you might need
to edit the formula to look something like

=IF(B1="Yes",VLOOKUP(A1,rates!$D$1:$E$4,2,FALSE),0)

Peace
EQC

 

[Dave Peterson]

If you really have a listbox that hovers above A1, you'll have to have a way to
get the value out of that listbox--maybe using the linked cell property???

After that, it still sounds like =vlookup() and =if() will work for you.

Debra Dalgleish has lots of notes:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://contextures.com/xlFunctions02.html#Trouble

 

[Kathleen]

Thank you both!! Its working very well.

If you really have a listbox that hovers above A1, you'll have to have a way to
get the value out of that listbox--maybe using the linked cell property???

After that, it still sounds like =vlookup() and =if() will work for you.

Debra Dalgleish has lots of notes:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://contextures.com/xlFunctions02.html#Trouble