Referencing Corresponding Values

[Guest]

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

 

[vezerid]

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:I32,1)))),I1:I32,0)),FIND("=",INDEX(H1:H32,MATCH(CHAR(MIN(CODE(RIGHT(I1:I32,1)))),I1:I32,0)))-2)

HTH
Kostis Vezerides

 

[vezerid]

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

 

[vezerid]

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(RIGHT(Sheet2!A2:A6,1)))),RIGHT(Sheet2!A2:A6,1),0)))-2)

HTH
Kostis Vezerides

 

[Guest]

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

 

[Domenic]

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(LOOKUP(CHAR(ROW(INDIRECT("
65:74"))),Sheet2!B3:B12,Sheet2!A3:A12),Sheet1!A2:E2,0)),0))

Hope this helps!

 

[Guest]

This produces a value of #N/A. Isn't there a simpler formula that works?

 

[Guest]

My mistake. This formula works. Thanks very much!

 

[Domenic]

You're very welcome! Actually, the formula can be shortened...

=INDEX(Sheet2!A3:A12,MATCH(TRUE,ISNUMBER(MATCH(LOOKUP(Sheet2!B3:B12,Sheet
2!B3:B12,Sheet2!A3:A12),Sheet1!A2:E2,0)),0))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!