Retrieve row 1 of column that matches data of a row

Question

I am working with two tables, and I am trying to pull the data from the first row in a column that has a value in that column that matches the data in A2.

Example tables...

ID#s  RESULT
9
17
49
43
5
32
46
56
20
36
37

test1   test2   test3   test4   test5   test6   test7
  1       9       17      25      33      41      49
  2       10      18      26      34      42      50
  3       11      19      27      35      43      51
  4       12      20      28      36      44      52
  5       13      21      29      37      45      53
  6       14      22      30      38      46      54
  7       15      23      31      39      47      55
  8       16      24      32      40      48      56

So basically, these are two different tables. Looking at the first table, I want the formula to go into B2 and find the value in A2 (In this case, the number 9) and then find it on the second table and return the value in the first row of the column the 9 is in. So B2 should return "test2" because it found the number 9 in the column for "test2". End result would look like the below...

ID#s  RESULT
9      test2
17     test3
49     test7
43     test6
5      test1
32     test4
46     test6
56     test7
20     test3
36     test5
37     test5

Any idea how I would go about this? Hopefully I have explained what I am requesting help with thoroughly.

Ron Rosenfeld Ron Rosenfeld · Accepted Answer · 2016-06-23T01:30:38

Try this formula entered by holding down ctrl+shift while hitting enter

=OFFSET(Table2,0,MAX(IF(A2=Table2,COLUMN(Table2)))-COLUMN(Table2))

Given your data, we return:

If you should happen to have Excel 2016, and also want to use a Table with structured references, you could, instead, use this formula (also entered with CSE):

=TEXTJOIN(,,IF(A2=myDataTable,myDataTable[#Headers],""))

Retrieve row 1 of column that matches data of a row

2 Answers