# Look up through Multiple Tables

#### haris1

##### New Member
Hi All,

Hope you are having a good time!

I was trying to do some lookup through multiple tables but could not do it. Need some help here I have a table from where i am trying to extract overall max value and then based off of some condition. There exits 1 more table with same header. Tables would be something as below-

 Table 1 Table 2 A B C D E A B C D E 10 4 3 63 19 Yes Yes No No No 10 2 1 0 0 No No No No Yes 61 26 99 70 56 Yes Yes Yes Yes No 20 3 3 60 25 No No No Yes Yes 0 0 0 0 0 No Yes Yes Yes Yes 55 7 98 45 9 No No No No No Max-Overall 63 Table 2 corresponding cell value ?? Max if Yes 10 Table 2 corresponding cell value ??

<colgroup><col span="5"><col><col><col span="5"></colgroup><tbody>
</tbody>
What I wanted to have is corresponding cell value from Table 2.

Thank you so much for your help,
Hari

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Here are some options:

ABCDEFGHIJK
110436319Max - Overall99
2102100Corresponding ValueNo
36126997056
420336025Max ifyes7070
500000Corresponding ValueyesYes
655798459
7
8
9YesYesNoNoNo
10NoNoNoNoYes
11YesYesNoYesNo
12NoNoNoYesYes
13NoYesYesYesYes
14NoNoNoNoNo

<tbody>
</tbody>
Sheet18

Worksheet Formulas
CellFormula
J1=MAX(A1:E6)
K4=MAXIFS(A1:E6,A9:E14,I4)
J5=I4

<tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
J2{=IF(COUNTIF(\$A\$1:\$E\$6,J1)>1,"Multiple matches",OFFSET(\$A\$9,MIN(IF(\$A\$1:\$E\$6=J1,ROW(\$A\$1:\$E\$6)-ROW(\$A\$1))),MIN(IF(\$A\$1:\$E\$6=J1,COLUMN(\$A\$1:\$E\$6)-COLUMN(\$A\$1)))))}
J4{=MAX(IF(\$A\$9:\$E\$14=I4,\$A\$1:\$E\$6))}
K5{=OFFSET(\$A\$9,MIN(IF(\$A\$1:\$E\$6=K4,ROW(\$A\$1:\$E\$6)-ROW(\$A\$1))),MIN(IF(\$A\$1:\$E\$6=K4,COLUMN(\$A\$1:\$E\$6)-COLUMN(\$A\$1))))}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

The MAX overall formula in J1 is about as simple as you can get. The formula in J2 to get the corresponding value is an array formula and must be confirmed with Control+Shift+Enter.

The formula in J4 will find the maximum value from table 1 if the corresponding value in table 2 is the value in I4. Of necessity, the corresponding value in J5 will just be what's in I4. If you have a newer version of Excel with the new function MAXIFS, you can use the non-array formula in K4 instead of the formula in J4.

Hope this helps.

Thank You so much Eric..May I request you to explain the logic behind this formula..

Here are some options:

ABCDEFGHIJK
110436319Max - Overall99
2102100Corresponding ValueNo
36126997056
420336025Max ifyes7070
500000Corresponding ValueyesYes
655798459
7
8
9YesYesNoNoNo
10NoNoNoNoYes
11YesYesNoYesNo
12NoNoNoYesYes
13NoYesYesYesYes
14NoNoNoNoNo

<tbody>
</tbody>
Sheet18

Worksheet Formulas
CellFormula
J1=MAX(A1:E6)
K4=MAXIFS(A1:E6,A9:E14,I4)
J5=I4

<tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
J2{=IF(COUNTIF(\$A\$1:\$E\$6,J1)>1,"Multiple matches",OFFSET(\$A\$9,MIN(IF(\$A\$1:\$E\$6=J1,ROW(\$A\$1:\$E\$6)-ROW(\$A\$1))),MIN(IF(\$A\$1:\$E\$6=J1,COLUMN(\$A\$1:\$E\$6)-COLUMN(\$A\$1)))))}
J4{=MAX(IF(\$A\$9:\$E\$14=I4,\$A\$1:\$E\$6))}
K5{=OFFSET(\$A\$9,MIN(IF(\$A\$1:\$E\$6=K4,ROW(\$A\$1:\$E\$6)-ROW(\$A\$1))),MIN(IF(\$A\$1:\$E\$6=K4,COLUMN(\$A\$1:\$E\$6)-COLUMN(\$A\$1))))}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

The MAX overall formula in J1 is about as simple as you can get. The formula in J2 to get the corresponding value is an array formula and must be confirmed with Control+Shift+Enter.

The formula in J4 will find the maximum value from table 1 if the corresponding value in table 2 is the value in I4. Of necessity, the corresponding value in J5 will just be what's in I4. If you have a newer version of Excel with the new function MAXIFS, you can use the non-array formula in K4 instead of the formula in J4.

Hope this helps.

Sure, I assume you mean the J2 formula.

The COUNTIF part just checks for multiple matches, and if it finds more than one, it says Multiple Matches and quits.

This part:
MIN(IF(\$A\$1:\$E\$6=J1,ROW(\$A\$1:\$E\$6)-ROW(\$A\$1)))

is the array part of the function. It checks every cell in the range A1:E6 to see if it matches the value in J1. If it does, it calculates the row number of that cell, minus the first row of the range to get an offset. There should only be 1 match (we checked with the COUNTIF), so we'll get an output array of {FALSE,FALSE,FALSE,1,FALSE, etc.}. The FALSE is the default result from the IF. Then the MIN gets the least value, ignoring non-numeric values, returning the row offset of the matching cell.

Then the same thing is done to get the column offset of the matching cell. Then we use OFFSET with the upper left corner of the second range, and give it the row and column offsets we just calculated to find the value in the same relative position.

Hope this helps.

Thank You Eric! You're welcome. Replies
11
Views
3K
Replies
1
Views
388
Replies
11
Views
3K
Replies
3
Views
210
Replies
2
Views
206

### Forum statistics

1,196,277
Messages
6,014,415
Members
441,818
Latest member
itsfaisalkhalid ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back