Please Help on vlookup and comboBox

[Allie Tang]

Hello,

I have this table in this order:

A B C D
1 Active
2 count row%
3 AK Active 26 ,89.6%
4 AK Foreclosure 1 ,33.3%
5 AK Bankruptcy . .
6 AK Payment Plan 6 ,4.8%
7 AK REO 1 ,0.24%
8 AR Active 94 ,69.1%
9 AR Foreclosure 6 ,3.7%
10AR Bankruptcy 8 ,2.0%
11AR Payment Plan 4 ,7.1%
12AR REO . .

I built a combo box by using the control toolbox and placed in the
same worksheet. The linkCell is placed in e15 and the listFillRange is
reading from another worksheet in this order
[Active,Foreclosure,Bankruptcy,Payment Plan and REO]. The formulas are
listed below:

cell[c19]: =IF(ISNONTEXT(VLOOKUP(E15,B37,2,0))=FALSE," ",VLOOKUP
($E$15,B37,2,0)&" "&VLOOKUP(E15,B37,3,0))
cell[c20]: =IF(ISNONTEXT(VLOOKUP(E15,B48,2,0))=FALSE,"
",VLOOKUP($E$15,B48,2,0)&" "&VLOOKUP(E15,B48,3,0))

When I select Active in the comboBox, the formula works well but if I
select Foreclosure,Bankruptcy,Payment Plan or REO, it repeatly returns
the first value in the table. For example, if I select Foreclosure in
comboBox, it returns 1,33.3% for both cells.

Active
AK ALASKA 1,33.3%
AR ARKANSAS 1,33.3%

which I would like to be:

Active
AK ALASKA 1,33.3%
AR ARKANSAS 6,3.7%

Did I setup my table correctly or do I need to rearrange the table? Do
I need to redo my formula?

If you have any suggestion, please let me know.

Thks for your time,
Allie

 

[Frank Kabel]

Hi
VLOOKUP will always return the first match in your range. In your case
you need to match to values.
if you enter the lookup value for column A in E15 and the value for
column B in F15 try the following array formula (entered with
CTRL+SHIFT+ENTER)
=INDEX($C$3:$C$12,MATCH(E15&F15,$A$3:$A$12&$B$3:$B$12,0))

assumption: column C contains your values