compicated lookup funtion
 

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=388001

Chris,

If I understood your question, you could concatinate the different values in
a new helper column on both of the worksheets. Then do your final lookup.

I.e, Insert a new Column before Column A

A1 could become (Risk Value from range XYZ via lookup)&D (via lookup or row
reference)&DA(via lookup or row reference)&CX(via lookup or row reference).

I am sure others will weigh in

HTH Dennis

"chrisrowe_cr" wrote:


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=388001

Dennis Wrote:
Chris,

If I understood your question, you could concatinate the different
values in
a new helper column on both of the worksheets. Then do your final
lookup.

I.e, Insert a new Column before Column A

A1 could become (Risk Value from range XYZ via lookup)&D (via lookup or
row
reference)&DA(via lookup or row reference)&CX(via lookup or row
reference).

I am sure others will weigh in

HTH Dennis

Dennis,

I did think about that, and saw some posts on it, however, the 1st work
sheet is non editable...

spreadsheet 1 (the non editable) contains details of thousands of
products, speadsheet 2 needs to lookup the top 1,2,3 of this products
and and plonk them in along with the product name.

ie spreadsheet 1:

Product Name data data data data RISK_VALUE data
PRODUCT_RANK

beans 22 3.7 2.1 1 2
8 3
apples 36 4.2 3 5 4
7 2
CHIPS 21 5 2 1 2
2 1

then in spreadsheet 2:

Product Name Rank
CHIPS 1

thats what i need to achieve if it helps


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

wooops dodgy space editing


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

=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))

This is the solution that a nice chap came up with, but I get a #-NAME-
error... can anyone 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=388001