1

I have some data that looks something like this

A B C D E F G
B C D E F G A
C D E F G A B
D E F G A B C 
E F G A B C D
F G A B C D E 
G A B C D E F 

In addition, I have a piece of data that looks like this:

A 
B 

What I need to lookup is the row header for the row where the top value resides, when the column header is the bottom value. For instance, in this example, I would look in the first row to find B, then I would look down the column until I found A, then I would look at the row header to return G

 A  (B) C D E F G
 B   C  D E F G A
 C   D  E F G A B
 D   E  F G A B C 
 E   F  G A B C D
 F   G  A B C D E 
(G) (A) B C D E F 

I've tried using HLOOKUP, INDEX, MATCH, etc., but I keep running into a roadblock when I need to provide an array for the whole column.

Here's one such example of the syntax that's giving me errors:

=CONCAT(address(8,1):address(12,1))

I can't have the : between my two cell addresses.

Any ideas?

  • Is the 1st row in your example the actual column labels, or the header row of a table at some random location? – fixer1234 Oct 6 '17 at 2:55
  • Should that : be ,":", – Tony Stewart Sunnyskyguy EE75 Oct 6 '17 at 3:22
  • @fixer1234 it's both :3 It's kind of a header, in that it's the reference for the rest of the columns, but it can also be the data (For example, if my data set was A A then it would return A) – phroureo Oct 6 '17 at 15:42
0

I'm sure there are more elegant ways to do this, but here's a quick and dirty solution.

enter image description here

I stuck your grid in an arbitrary location (B2:H8). J2 and J3 are your lookup values and the result is in J4. What is in column L is components of the answer for the purposes of explanation because the formula gets long.

The first piece is in L3, finding the B target in the top row:

=MATCH(J3,B2:H2,0)

It finds the target B in column 2 of the array, and this is used in the next step of finding A in that column. L2 contains:

=MATCH(J2,OFFSET(B2,0,L3-1,7,1),0)

OFFSET is anchored to the top left corner of the array (B2). The column that B was found in was the second column of the array, but that is an offset of 1 from the first column, hence the -1 (I love it when I get to use "hence" in a sentence). OFFSET lets you specify a range using height and width parameters. The 7 is the array height in this example.

MATCH finds the target A in row 7 of that column of the array.

Now you just need to go to that row of the first column of the array:

=INDEX(B2:B8,L2,0)

There, you find your answer: G

You can substitute the component formulas for the components to get a self-contained, stand-alone formula:

=INDEX(B2:B8,MATCH(J2,OFFSET(B2,0,MATCH(J3,B2:H2,0)-1,7,1),0),0)
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.