Complicated lookup function
 

--------------------------------------------------------------------------------

Hi all,

Right, im working between 2 spreadsheets, pulling accross large amounts
of data, so a lookup seems the most obvious way forward.

Problem is that I need to look up multiple values. I need to lookup the
risk value '2' in range xyz and then find the product in that range with
a 'x' rating and return its name. The product name is in col D, the
value in col DA and the rating in col CX

If that doesnt make sence in short I need to lookup the number 'x'
rated product with a risk value of 'x' in the range xyz and return the
products name,

can someone pls help?


--
chrisrowe_cr
------------------------------------------------------------------------
chrisrowe_cr's Profile: http://www.excelforum.com/member.php...o&userid=25220
View this thread: http://www.excelforum.com/showthread...hreadid=388002

Hi!

=INDEX(col D range,MATCH(1,(col DA range=value)*(col CX range="rating"),0))

Entered as an array using the key combo of CTRL,SHIFT,ENTER.

Biff

"chrisrowe_cr"
wrote in message
...

--------------------------------------------------------------------------------

Hi all,

Right, im working between 2 spreadsheets, pulling accross large amounts
of data, so a lookup seems the most obvious way forward.

Problem is that I need to look up multiple values. I need to lookup the
risk value '2' in range xyz and then find the product in that range with
a 'x' rating and return its name. The product name is in col D, the
value in col DA and the rating in col CX

If that doesnt make sence in short I need to lookup the number 'x'
rated product with a risk value of 'x' in the range xyz and return the
products name,

can someone pls help?


--
chrisrowe_cr
------------------------------------------------------------------------
chrisrowe_cr's Profile:
http://www.excelforum.com/member.php...o&userid=25220
View this thread: http://www.excelforum.com/showthread...hreadid=388002

whats the CTRL SHIFT ENTER bit mean?


--
chrisrowe_cr
------------------------------------------------------------------------
chrisrowe_cr's Profile: http://www.excelforum.com/member.php...o&userid=25220
View this thread: http://www.excelforum.com/showthread...hreadid=388002

=INDEX(col D '[PPfV Fund Analysis Plus 30Jun05.xls]input
plus'!$C$922:$DO$965,MATCH(1,(col DJ '[PPfV Fund Analysis Plus
30Jun05.xls]input plus'!$C$922:$DO$965=B5)*(col DG '[PPfV Fund Analysis
Plus 30Jun05.xls]input plus'!$C$922:$DO$965=1),0))

ok thats what it looks like, but it returns a #-NAME- error


--
chrisrowe_cr
------------------------------------------------------------------------
chrisrowe_cr's Profile: http://www.excelforum.com/member.php...o&userid=25220
View this thread: http://www.excelforum.com/showthread...hreadid=388002

Well, let's see what I can interpret from that.....

Tip = long file names (and/or sheet names) suck!

=INDEX('[PPfV Fund Analysis Plus 30Jun05.xls]input
plus'!$D$922:$D$965,MATCH(1,('[PPfV Fund Analysis Plus 30Jun05.xls]input
plus'!$DJ$922:$DJ$965=B5)*('[PPfV Fund Analysis Plus 30Jun05.xls]input
plus'$DG$922:$DG$965=1),0))

This is an array formula. When you type it into a cell, instead of just
hitting the ENTER key like you normally would, you MUST use the key
combination of CTRL SHIFT ENTER. Hold down the CTRL and SHIFT keys, then hit
ENTER When done properly Excel will place squiggly braces { } around the
formula. You MUST use the key combo to do this. You can't just type the
braces in.

Biff

"chrisrowe_cr"
wrote in message
news:chrisrowe_cr.1seyii_1121771232.2128@excelforu m-nospam.com...

=INDEX(col D '[PPfV Fund Analysis Plus 30Jun05.xls]input
plus'!$C$922:$DO$965,MATCH(1,(col DJ '[PPfV Fund Analysis Plus
30Jun05.xls]input plus'!$C$922:$DO$965=B5)*(col DG '[PPfV Fund Analysis
Plus 30Jun05.xls]input plus'!$C$922:$DO$965=1),0))

ok thats what it looks like, but it returns a #-NAME- error


--
chrisrowe_cr
------------------------------------------------------------------------
chrisrowe_cr's Profile:
http://www.excelforum.com/member.php...o&userid=25220
View this thread: http://www.excelforum.com/showthread...hreadid=388002