ExcelBanter - View Single Post

smartin

wrote:
Hi friends,

Could anybody suggest me if i serching for more than single lookup
value how will be the vlookup formula?

For e.g

If i need Lookup value A with combination of 11 and 12 which are in
another two diff.columns and i need those two as well. So, can i put
three criterias to get very specific output?

Thanks in Advance for ur help freind !

Yogi

Hi Yogi, VLOOKUP cannot do this, but there are ways.

If I understand you correctly your data looks something like this:

column: A B C D
data: A 11 12 Target

and you wish to obtain column D (Target).

If Target is numeric, SUMPRODUCT works well:

=SUMPRODUCT(--(J1=$A$1:$A$99),--(K1=$B$1:$B$99),--(L1=$C$1:$C$99),$D$1:$D$99)

J, K and L contain your search terms, e.g., "A", 11, and 12, or you can
go without the references and hard-code the search terms. $99 is an
arbitrarily large row reference to contain the lookup area.

If Target might not be numeric, a different approach is necessary. The
following must be entered as an array* formula:

=INDEX($D$1:$D$99,MATCH(1,(J1=$A$1:$A$99)*(K1=$B$1 :$B$99)*(L1=$C$1:$C$99),0))

*Commit the array formula by pressing Ctrl+Shift+Enter, do not just
press Enter or Tab.

Hope this helps.