## Reverse two-way lookup in a cross reference table

The table of contents below lets you quickly navigate to the formula you are looking for.

The excel 2016 formula lets you concatenate all values in one cell and it works only in excel 2016 because of the TEXTJOIN function. You only need one formula and it is small and easy to understand.

The version that works with all excel versions lets you put values in a cell each however the formulas are a little bit more complicated.

#### Table of Contents

- Reverse two-way lookups in a cross reference table [Excel 2016]
- Reverse two-way lookups in a cross reference table [All excel versions]
- Reverse two-way lookup in a cross reference table
- Functions in this post

table

Row 2 contains variable and Column B contains another variable

The combination of 32 with 51 gives me 0.9 or cell D4

I need to report which 2 and B combinations give me values between 0.4 and 0.5. In this case, it would be 31-53 and 32-50.

I could rearrange this to be in 3 columns with the results on the last column and use something like below (The equation does not represent the example I gave.)

IFERROR(INDEX(array, SMALL(IF((min=data), ROW($B$2:$B$10)-1), ROW(A3)), COLUMN(A3)), " ")

This works but my excel table would have too many rows and the initial data set comes the other way so it would take some time to convert it. The original matrix is more compact.

I am trying to convert this equation but I am having trouble matching it to an array instead to just one column.

The following array formula returns multiple values from a cross reference table if they meet specific critera.

**Array formula in cell H3:**

If you prefer having the values in a cell each instead of concatenated values in one cell, go to this part of this article.

#### How to build an array formula

If you did above steps correctly excel automatically adds a beginning and ending curly bracket to the formula, like this:

Don't enter these characters yourself.

#### Explaining array formula in cell G3

Note, TEXTJOIN function works only in Excel 2016. If you have an earlier version of excel, use this formula.

*Step 1 - Check if values in cell range C3:E5 are larger than H1 and smaller than H2*

IF(($C$3:$E$5>=$H$1)*($C$3:$E$5<=$H$2), $B$3:$B$5&"-"&$C$2:$E$2,"")

becomes

IF({0,0,1;1,0,0;0,0,0}, $B$3:$B$5&"-"&$C$2:$E$2,"")

*Step 2 - If TRUE concatenate corresponding values in row 2 and column B*

IF({0,0,1;1,0,0;0,0,0}, $B$3:$B$5&"-"&$C$2:$E$2,"")

becomes

IF({0,0,1;1,0,0;0,0,0}, {"31-50","31-51","31-53";"32-50","32-51","32-53";"33-50","33-51","33-53"},"")

and returns

{"","","31-53";"32-50","","";"","",""}

*Step 3 - Concatenate values using TEXTJOIN function*

TEXTJOIN(",",TRUE,{"","","31-53";"32-50","","";"","",""})

returns

31-53. 32-50 in cell H3.

Link to two-way lookups in an index table:

Looking up data in a cross reference table

#### Download excel *.xlsx file

Reverse two-way lookup in a cross reference table.xlsx

### Reverse two-way lookups in a cross reference table

This formula is for excel versions that don't have the TEXTJOIN function or if you prefer having the values in a cell each instead of concatenated values in one cell.

**Array formula in cell G7:**

**Array formula in cell H7:**

**Array formula in cell I7:**

#### Explaining formula in cell G7

#### Explaining formula in cell H7

#### Explaining formula in cell I7

#### Download excel *.xlsx file

Reverse-two-way-lookup-in-a-cross-reference-tablev3.xlsx

### Reverse two-way lookup in a cross reference table

The following array formulas returns a single value from a cross reference table.

**Array formula in cell E15:**

**Array formula in cell E16:**

#### Explaining array formula in cell E15

*Step 1 - Check what values in cell range equals value in E14 and return smallest row*

MIN(IF(B3:K12=E14, MATCH(ROW(B3:K12), ROW(B3:K12)), ""))

returns 5.

**Step 2 - Return corresponding header value**

=INDEX($A$3:$A$12, 5)

returns H.

#### Download excel *.xlsx file

Reverse two-way lookup in a cross reference tablev2.xlsx

#### Functions in this post:

**TEXTJOIN**(*delimiter*, *ignore_empty*, *text1*, ...)

Concatenates a list or range of text strings using a delimiter

**IF**(*logical_test*, [*value_if_true*], [*value_if_false*])

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

**MATCH(**lookup_value, lookup_array, [match_type]**)**

Returns the relative position of an item in an array that matches a specified value

**INDEX(**array,row_num,[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**ROW(**reference**)**

Returns the rownumber of a reference

### Category: Cross reference table

Looking up data in a cross reference table

Question: How would I go about looking up data in an cross reference table. I have the header row (i.e. […]Comments(19) Filed in category: Cross reference table, Excel

Comments(14) Filed in category: Cross reference table, Excel, INDEX function

Comments(5) Filed in category: Conditional formatting, Cross reference table, Excel, Schedule, Templates

Excel udf: Looking up data in multiple cross reference tables

This post describes how to lookup values in multiple cross reference tables using a user defined function. This custom function […]Comments(3) Filed in category: Cross reference table

Use VLOOKUP to calculate discount percentages

Have you ever tried to build a formula to calculate discounts depending on price? The VLOOKUP function is much easier to […]Comments(1) Filed in category: Cross reference table, Excel, Vlookup

### 4 Responses to “Reverse two-way lookup in a cross reference table”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

[…] https://www.get-digital-help.com/2017/05/16/reverse-two-way-lookup-in-a-cross-reference-table/ […]

Hi Oscar,

Thank you very much.

Is there a way to report each combination with its corresponding results so in the example provided you would see

Column J Column K Column L

31 53 0.4

32 50 0.5

or

Column J Column K

31-53 0.4

32-50 0.5

Thanks Again.

Polar

(P.S Sorry this is a repost from the old thread. Just making sure I am not confusing things).

Polar,

Yes, it is possible. Read this.

Thank you!