Look up through Multiple Tables

haris1

New Member
Joined
Dec 10, 2015
Messages
39
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 1Table 2
ABCDEABCDE
10436319YesYesNoNoNo
102100NoNoNoNoYes
6126997056YesYesYesYesNo
20336025NoNoNoYesYes
00000NoYesYesYesYes
55798459NoNoNoNoNo
Max-Overall63
Table 2 corresponding cell value??
Max if Yes10
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,210
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top