Matching unsorted lookup values
 

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

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

Hi!

Are you not following any of your previous threads?

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

=INDEX(A$2:A$7,MATCH(SMALL(IF(B$2:B$7=2,C$2:C$7),R OW(1:1)),C$2:C$7,0))

Copied down will return:

Racing
Surfing
Rallying
Skiing
#NUM!




#NUM! indicates that there are no more matches. You can suppress the #NUM!
displays either by including an error trap in the formula or using
conditional formatting. If I recall correctly, the references in the actual
formula you would use are extremely long due to needing path and file names
so if I were you I would opt for the conditional formatting method of hiding
the #NUM! errors.

Biff

"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

Here's a link to download the sample file:
http://www.savefile.com/files/9866117
File: Matching unsorted lookup values_chrisrowe_cr_wksht
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Just a clarification:
The suggested set-up contains an implicit tie-breaker for col C ("Rating")
in Sheet1. Should be any ties in the "Ratings" col for any given value in
the "Risk Val", the set-up in Sheet2 will still extract *all* the desired
results, with any tied rating lines appearing in the same relative order as
they are in Sheet1.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----