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...o&userid=36949
View this thread: http://www.excelforum.com/showthread...hreadid=566586

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

"ramalana" wrote in message
...
|
| 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...o&userid=36949
| View this thread: http://www.excelforum.com/showthread...hreadid=566586
|

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=0 15"),0),1)

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

HTH

"ramalana" wrote:


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...o&userid=36949
View this thread: http://www.excelforum.com/showthread...hreadid=566586

..... typo ..

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

"Toppers" wrote:

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=0 15"),0),1)

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

HTH

"ramalana" wrote:


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...o&userid=36949
View this thread: http://www.excelforum.com/showthread...hreadid=566586

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.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"ramalana" wrote in
message ...

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...o&userid=36949
View this thread: http://www.excelforum.com/showthread...hreadid=566586

Sorry, a typo there!


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

--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )