Vlookup and multiple look ups

[Graeme]

I am tyring to write a vlookup formula which will lookup a block of data and
return a value based on multiuple conditions rather than just one. (i.e.
instead of looking up to match one cell value in the block of data I want to
match 2 cell values, prior to returning a result)

Is this possible using the vlookup function or is there an alternative
method I should use to achieve this.

Any help would be much appreciated.

 

[Frank Kabel]

Hi
use the following array formula (entered with CTRL+SHIFT+ENTER):
=INDEX(C1:C100,MATCH(1,(A1:A100="condition1")*(B1:B100="condition2"),0)
)

 

[duane]

see sumproduct discussions or

http://www.xldynamic.com/source/xld.SUMPRODUCT.htm

 

[Graeme]

Frank,

Thanks for that. One supplmentary question I would have though is - is your
formual looking up the same value (1) in two columns of data? What I am
looking to do is lookup 2 seperate values/conditions, match them and then
return a value if both values conditions/match.

Will this formula still work or am I misunderstanding it?

Regards
Graeme

 

[Frank Kabel]

Hi
just try it
The '1' is the return of an multiplication of true logical statements.
If both are true they return
TRUE*TRUE=1*1=1

 

[Graeme]

Almost there.

When evaluating the formula in excel formual is evaluating to
INDEX(RESULT COLUMN ARRAY,MATCH(1,1,0))
which is then evaluating to
INDEX(RESULT COLUMN ARRAY,#N/A))

As both conditions are true this should bring back a result however stil
have something wrong obviously. Any thoughts?

Thanks for you help re-this

Regards
Graeme

 

[Dave Peterson]

Are you using "Tools|formula auditing|evaluate formula" to get these results?
(this was added in xl2002.)

If yes, I could get your same situation if I evaluated the formula that wasn't
array entered.

If I array entered the formula, then that new feature worked fine.