compicated lookup funtion

[chrisrowe_cr]

Hi all,

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

Problem is that I need to look up multiple values. I need to looku
the risk value '2' in range xyz and then find the product in that rang
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 th
products name,

can someone pls help

 

[Dennis]

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]

Chris,

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

I.e, Insert a new Column before Column A

A1 could become (Risk Value from range XYZ via lookup)&D (via lookup o
row
reference)&DA(via lookup or row reference)&CX(via lookup or ro
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 wor
sheet is non editable...

spreadsheet 1 (the non editable) contains details of thousands o
products, speadsheet 2 needs to lookup the top 1,2,3 of this product
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

then in spreadsheet 2:

Product Name Rank
CHIPS 1

thats what i need to achieve if it help

 

[chrisrowe_cr]

wooops dodgy space editin

 

[chrisrowe_cr]

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