Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 ---- |
#5
|
|||
|
|||
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 ---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I LOOKUP text values | Excel Worksheet Functions | |||
Lookup Table Dilemma | Excel Worksheet Functions | |||
Lookup Function - Specific Values | Excel Worksheet Functions | |||
Need help with lookup and comparing values | Excel Worksheet Functions | |||
Formula for Searching & matching two values in excel | Excel Discussion (Misc queries) |