Multiple VLOOKUP

R

ramalana

Hello

I am trying to return a value based on a multiple VLOOKUP

i.e. spreadsheet contains multiple lines for given value, say 322654
and also multiple lines for second lookup value of 015, although ther
will only be one 015 for 322654. I would like to return a related cel
that fulfills both of these conditions.

Alternatively perhaps I could use a VLOOKUP with an IF function buil
in, any ideas please let me know.

Many thank
 
N

Niek Otten

Insert a column in which you concatenate the two key columns, and do a VLOOKUP with concatenated cells

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|
| Hello
|
| I am trying to return a value based on a multiple VLOOKUP
|
| i.e. spreadsheet contains multiple lines for given value, say 322654,
| and also multiple lines for second lookup value of 015, although there
| will only be one 015 for 322654. I would like to return a related cell
| that fulfills both of these conditions.
|
| Alternatively perhaps I could use a VLOOKUP with an IF function built
| in, any ideas please let me know.
|
| Many thanks
|
|
| --
| ramalana
| ------------------------------------------------------------------------
| ramalana's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36949
| View this thread: http://www.excelforum.com/showthread.php?threadid=566586
|
 
T

Toppers

Assuming value required is in column C and columns A & B contain your "search
parameters" then:

=INDEX(C1:C100,MATCH(1,(A1:A100=322654)*(B1:B100=015"),0),1)

Enter with Control+Shift+Enter (as an array formula). Curly brackets {} will
appear around the formula if it is entered correctly.

HTH
 
A

Arvi Laanemets

Hi

The solution depends on your data. When the return value is numeric and
combined conditions always determine a single row or none, you can use
SUMPRODUCT function, like
=SUMPRODUCT(--(Sheet1!A1:$A$1000=322654),--Sheet1!$B$1:$B$1000="015",Sheet1!$C$1:$C$1000)
(when there are several rows matching all conditions, the formula returns
the sum of according values - in my example in column C)

When the return value is string, you have to use array formulas or helper
columns, like explained in previous answers.
 
A

Arvi Laanemets

Sorry, a typo there!


=SUMPRODUCT(--(Sheet1!A1:$A$1000=322654),--(Sheet1!$B$1:$B$1000="015"),Sheet1!$C$1:$C$1000)
 
Top