Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Referencing Corresponding Values
I want to know how to create a formula that will reference a set of values in
Sheet2 and display the lowest corresponding value in ascending order in Sheet1. To be more specific, I have 5 text strings in 5 cells, A2 through A6, in Sheet1. I want to select the corresponding string in Shee2 and place it in A7, based on values I have assigned in Sheet2. Sheet 1: A2 = Arenas A3 = Haywood A4 = Hughes A5 = Jamison A6 = Jeffries Sheet 2: Arenas = A Haywood = J Hughes = B Jamison = H Jeffries = I In Sheet2, the string that corresponds to the lowest letter (Arenas, A) is to be placed in A7 in Sheet1. How do I set this up? Thanks in advance, Bob |
#2
|
|||
|
|||
Referencing Corresponding Values
Bob,
I am assuming that the cells in Sheet2 look exactly as you show them here, i.e. they are not broken in two columns. Thus the formula has to look after the " = " and return the part before. In Sheet1!A7: =LEFT(INDEX(H1:H32,MATCH(CHAR(MIN(CODE(RIGHT(I1:I3 2,1)))),I1:I32,0)),FIND("=",INDEX(H1:H32,MATCH(CHA R(MIN(CODE(RIGHT(I1:I32,1)))),I1:I32,0)))-2) HTH Kostis Vezerides |
#3
|
|||
|
|||
Referencing Corresponding Values
Bob, I apologize for the earlier formula, I forgot to change the cell
references from my own workbook where I was testing it. But I used different data from a problem that looked similar. Please disregard the formula, as I may have too misunderstood your intentions. Right back if my assumptions are correct or not, maybe a much simpler solution exists to your problem. Kostis |
#4
|
|||
|
|||
Referencing Corresponding Values
OK, I had some time and tested this variant, which is correct.
In Sheet1!A7: =LEFT(INDEX(Sheet2!A2:A6,MATCH(CHAR(MIN(CODE(RIGHT (Sheet2!A2:A6,1)))),RIGHT(Sheet2!A2:A6,1),0)), FIND("=",INDEX(Sheet2!A2:A6,MATCH(CHAR(MIN(CODE(RI GHT(Sheet2!A2:A6,1)))),RIGHT(Sheet2!A2:A6,1),0)))-2) HTH Kostis Vezerides |
#5
|
|||
|
|||
Referencing Corresponding Values
Kostas,
This formula returns a value of #VALUE! Let me explain my problem in another way. In Sheet1, there is a list of 5 names. A2 = Arenas B2 = Haywood C2 = Hughes D2 = Jamison E2 = Jeffries In Sheet2, there is a list of 10 names and a letter (A through J) beside each name. The 5 names in Sheet1 are included in the 10 names in Sheet2. A3 = Blake B3 = F A4 = Arenas B4 = B A5 = Hughes B5 = A A6 = Dixon B6 = C A7 = Profit B7 = D A8 = Peeler B8 = E A9 = Hayes B9 = G A10 = Haywood B10 = J A11 = Jamison B11 = H A12 = Jeffries B12 = I I want to determine the smallest letter (A) from Sheet2 and place the corresponding name (Hughes) in Sheet1 cell F2--only if that name appears on the list in Sheet1. If not, I want to find the next smallest letter with a matching name. Can you help? Thanks very much, Bob "vezerid" wrote: OK, I had some time and tested this variant, which is correct. In Sheet1!A7: =LEFT(INDEX(Sheet2!A2:A6,MATCH(CHAR(MIN(CODE(RIGHT (Sheet2!A2:A6,1)))),RIGHT(Sheet2!A2:A6,1),0)), FIND("=",INDEX(Sheet2!A2:A6,MATCH(CHAR(MIN(CODE(RI GHT(Sheet2!A2:A6,1)))),RIGHT(Sheet2!A2:A6,1),0)))-2) HTH Kostis Vezerides |
#6
|
|||
|
|||
Referencing Corresponding Values
First, sort your table by Column B, in ascending order, as follows:
Hughes A Arenas B Dixon C Profit D Peeler E Blake F Hayes G Jamison H Jeffries I Haywood J Then try the following array formula that needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER... =INDEX(Sheet2!A3:A12,MATCH(TRUE,ISNUMBER(MATCH(LOO KUP(CHAR(ROW(INDIRECT(" 65:74"))),Sheet2!B3:B12,Sheet2!A3:A12),Sheet1!A2:E 2,0)),0)) Hope this helps! In article , bob wrote: Let me explain my problem in another way. In Sheet1, there is a list of 5 names. A2 = Arenas B2 = Haywood C2 = Hughes D2 = Jamison E2 = Jeffries In Sheet2, there is a list of 10 names and a letter (A through J) beside each name. The 5 names in Sheet1 are included in the 10 names in Sheet2. A3 = Blake B3 = F A4 = Arenas B4 = B A5 = Hughes B5 = A A6 = Dixon B6 = C A7 = Profit B7 = D A8 = Peeler B8 = E A9 = Hayes B9 = G A10 = Haywood B10 = J A11 = Jamison B11 = H A12 = Jeffries B12 = I I want to determine the smallest letter (A) from Sheet2 and place the corresponding name (Hughes) in Sheet1 cell F2--only if that name appears on the list in Sheet1. If not, I want to find the next smallest letter with a matching name. Can you help? Thanks very much, Bob |
#7
|
|||
|
|||
Referencing Corresponding Values
This produces a value of #N/A. Isn't there a simpler formula that works?
"Domenic" wrote: First, sort your table by Column B, in ascending order, as follows: Hughes A Arenas B Dixon C Profit D Peeler E Blake F Hayes G Jamison H Jeffries I Haywood J Then try the following array formula that needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER... =INDEX(Sheet2!A3:A12,MATCH(TRUE,ISNUMBER(MATCH(LOO KUP(CHAR(ROW(INDIRECT(" 65:74"))),Sheet2!B3:B12,Sheet2!A3:A12),Sheet1!A2:E 2,0)),0)) Hope this helps! In article , bob wrote: Let me explain my problem in another way. In Sheet1, there is a list of 5 names. A2 = Arenas B2 = Haywood C2 = Hughes D2 = Jamison E2 = Jeffries In Sheet2, there is a list of 10 names and a letter (A through J) beside each name. The 5 names in Sheet1 are included in the 10 names in Sheet2. A3 = Blake B3 = F A4 = Arenas B4 = B A5 = Hughes B5 = A A6 = Dixon B6 = C A7 = Profit B7 = D A8 = Peeler B8 = E A9 = Hayes B9 = G A10 = Haywood B10 = J A11 = Jamison B11 = H A12 = Jeffries B12 = I I want to determine the smallest letter (A) from Sheet2 and place the corresponding name (Hughes) in Sheet1 cell F2--only if that name appears on the list in Sheet1. If not, I want to find the next smallest letter with a matching name. Can you help? Thanks very much, Bob |
#8
|
|||
|
|||
Referencing Corresponding Values
My mistake. This formula works. Thanks very much!
"Domenic" wrote: First, sort your table by Column B, in ascending order, as follows: Hughes A Arenas B Dixon C Profit D Peeler E Blake F Hayes G Jamison H Jeffries I Haywood J Then try the following array formula that needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER... =INDEX(Sheet2!A3:A12,MATCH(TRUE,ISNUMBER(MATCH(LOO KUP(CHAR(ROW(INDIRECT(" 65:74"))),Sheet2!B3:B12,Sheet2!A3:A12),Sheet1!A2:E 2,0)),0)) Hope this helps! In article , bob wrote: Let me explain my problem in another way. In Sheet1, there is a list of 5 names. A2 = Arenas B2 = Haywood C2 = Hughes D2 = Jamison E2 = Jeffries In Sheet2, there is a list of 10 names and a letter (A through J) beside each name. The 5 names in Sheet1 are included in the 10 names in Sheet2. A3 = Blake B3 = F A4 = Arenas B4 = B A5 = Hughes B5 = A A6 = Dixon B6 = C A7 = Profit B7 = D A8 = Peeler B8 = E A9 = Hayes B9 = G A10 = Haywood B10 = J A11 = Jamison B11 = H A12 = Jeffries B12 = I I want to determine the smallest letter (A) from Sheet2 and place the corresponding name (Hughes) in Sheet1 cell F2--only if that name appears on the list in Sheet1. If not, I want to find the next smallest letter with a matching name. Can you help? Thanks very much, Bob |
#9
|
|||
|
|||
Referencing Corresponding Values
You're very welcome! Actually, the formula can be shortened...
=INDEX(Sheet2!A3:A12,MATCH(TRUE,ISNUMBER(MATCH(LOO KUP(Sheet2!B3:B12,Sheet 2!B3:B12,Sheet2!A3:A12),Sheet1!A2:E2,0)),0)) ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , bob wrote: My mistake. This formula works. Thanks very much! "Domenic" wrote: First, sort your table by Column B, in ascending order, as follows: Hughes A Arenas B Dixon C Profit D Peeler E Blake F Hayes G Jamison H Jeffries I Haywood J Then try the following array formula that needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER... =INDEX(Sheet2!A3:A12,MATCH(TRUE,ISNUMBER(MATCH(LOO KUP(CHAR(ROW(INDIRECT(" 65:74"))),Sheet2!B3:B12,Sheet2!A3:A12),Sheet1!A2:E 2,0)),0)) Hope this helps! In article , bob wrote: Let me explain my problem in another way. In Sheet1, there is a list of 5 names. A2 = Arenas B2 = Haywood C2 = Hughes D2 = Jamison E2 = Jeffries In Sheet2, there is a list of 10 names and a letter (A through J) beside each name. The 5 names in Sheet1 are included in the 10 names in Sheet2. A3 = Blake B3 = F A4 = Arenas B4 = B A5 = Hughes B5 = A A6 = Dixon B6 = C A7 = Profit B7 = D A8 = Peeler B8 = E A9 = Hayes B9 = G A10 = Haywood B10 = J A11 = Jamison B11 = H A12 = Jeffries B12 = I I want to determine the smallest letter (A) from Sheet2 and place the corresponding name (Hughes) in Sheet1 cell F2--only if that name appears on the list in Sheet1. If not, I want to find the next smallest letter with a matching name. Can you help? Thanks very much, Bob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Match function with duplicate values in an array | Excel Worksheet Functions | |||
Min values in a list of numbers | Excel Worksheet Functions | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
Combine FREQUENCY and SUM of Associated Values | Excel Worksheet Functions | |||
Second serie doesn't use X-as values | Charts and Charting in Excel |