VLOOKUP Limitation and Solution?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I am having problem with VLOOKUP that can solve my problem.

I have the following table with the following condition. For example, if A
meet both condition 1 and 2, it should return a result of "AA".

Data Condition 1 Condition 2 Results
A 1 ! AA
B 2 @ BB
C 3 # CC
D 4 $ DD
E 5 % EE

What is the function that I can use in order to lookup the data with the
following inputs so that it can return the correct results based on the above
condition?

Data Input 1 Input 2 Results
A 1 @
A 1 ! ------> expect to give AA
C 6 !
D 4 $ ------> expect to give DD
D 3 $
C 4 #
E 5 % ------> expect to give EE
 
=INDEX(C1:C100,MATCH(1,(A1:A100=2)*(B1:B100="@"),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
You could create a new helper column to the left of your existing VLOOKUP
table and therein CONCATENATE your Condition1 and Condition2 columns, to read:
1!
2@
3#
4$
5%
......and then do your VLOOKUP formula on this column.........it's like
having a 2-condition AND built into a VLOOKUP all at one time.....

hth
Vaya con Dios,
Chuck, CABGx3
 
Back
Top