One play which could deliver ..
Assume this table is in Sheet1, cols A to C,
data from row2 down
Product Type.........Risk Val.......Rating
Surfing........................2...............2
Racing.........................2...............1
Skiing..........................2...............6
Diving..........................3...............3
Rallying.......................2................5
Bungee Jumping..........3...............4
Using 2 empty cols to the right, say, cols E and F
Put in E2:
=IF(B2="","",IF(B2=F$1,C2+ROW()/10^10,""))
Copy E2 down to say, E100,
to cover the max expected data in cols A to C
(leave E1 empty)
Put in F1:
=IF(Sheet2!A1="","",Sheet2!A1)
In Sheet2
----------
Let's reserve cell A1 for input of the risk value of interest
Enter in A1, say: 2
(Or we could create a simple DV** list in A1 to select the risk values)
Copy paste the headers into A2:C2, viz:
Product Type.........Risk Val.......Rating
Put in A3:
=IF(ISERROR(SMALL(Sheet1!$E:$E,ROWS($A$1:A1))),"", INDEX(Sheet1!A:A,MATCH(SMA
LL(Sheet1!$E:$E,ROWS($A$1:A1)),Sheet1!$E:$E,0)))
Copy A3 across to C3, fill down to C101
(cover the sane range size as done in col E in Sheet1)
Sheet2 will return the results you're after
If cell A1 contains: 2, for the sample data, you'd get:
2
Product Type.........Risk Val.......Rating
Racing.........................2...............1
Surfing........................2...............2
Rallying.......................2................5
Skiing..........................2...............6
(blanks below)
And if cell A1 is changed to contain: 3, you'd get:
3
Product Type.........Risk Val.......Rating
Diving..........................3...............3
Bungee Jumping..........3...............4
(blanks below)
Adapt to suit ..
**Data validation list, viz.:
Select A1
Click Data Validation
Under Allow, select: List
Put in the "Source:" box: 1,2,3,4,5 (say)
Click OK
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"chrisrowe_cr"
wrote in message
news:chrisrowe_cr.1sp56j_1122246315.5917@excelforu m-nospam.com...
Hi all,
I have still not managed to my formula working. The folling data is on
one workbook.
Product Type.........Risk Val.......Rating
Surfing........................2...............2
Racing.........................2...............1
Skiing..........................2...............6
Diving..........................3...............3
Rallying.......................2................5
Bungee Jumping..........3...............4
if the prod type is in A2:A7, the risk val is B2:B7 and the rating in
C2:C7 im trying to look up the product name of the highest ranked
product with a risk val of 2. For instance, in my second workbook I
would want to look up RACING. I want to be able to repeat this process
to look up the next highest and so on with SURFING next followed by
RALLYING. You'll notice that the items with a higher risk value are to
be ignored. I desperatly need to get this done, much appreciate any
help. Chris.
--
chrisrowe_cr
------------------------------------------------------------------------
chrisrowe_cr's Profile:
http://www.excelforum.com/member.php...o&userid=25220
View this thread: http://www.excelforum.com/showthread...hreadid=389758