x
Solved

# Please help:  Trying to have Excel return value from 2 different tables (skull scratcher).  [Highest Points]

Posted on 2016-08-22
Medium Priority
42 Views
Hi Experts,

Trying to have Excel return a value in a table based on lookup from two separate tables.

So, populate col J with "ABC IDs from Tab "ABC IDs"
I suspect a combination of columns F and I, but is it possible in vLookup..?
I also heard of "Index + Match" (too complex)

Thankx
Yan
ExpExch-DIRF-Detail-Aug-22.xlsx
0
Question by:Ayansane
• 3
• 2

LVL 143

Expert Comment

ID: 41766448
either you put a column into ABC IDs that computes the concatenated value of Track and Region, and use vlookup on that, or you may try sumifs:
http://www.excel-university.com/vlookup-on-two-or-more-criteria-columns/
0

LVL 143

Assisted Solution

Guy Hengel [angelIII / a3] earned 1000 total points (awarded by participants)
ID: 41766452
in concrete, the SUMIFS expression to put into I3 would be:
=SUMIFS('ABC IDs'!D\$2:D\$25;'ABC IDs'!B\$2:B\$25;Detail!H3;'ABC IDs'!C\$2:C\$25;Detail!E3)
and then just copy down
1

LVL 36

Accepted Solution

Subodh Tiwari (Neeraj) earned 1000 total points (awarded by participants)
ID: 41766482
The same SUMIFS formula can be written with the help of Index/Match like below.....

On Details Sheet
In I3
``````=INDEX('ABC IDs'!\$D\$3:\$D\$25,MATCH(E3&H3,INDEX('ABC IDs'!\$C\$3:\$C\$25&'ABC IDs'!\$B\$3:\$B\$25,0),0))
``````
and copy down.

PS. Please set the Calculation Options to Automatic by going to the Formula Tab --> Calculation Options --> Choose Automatic so that when you copy down the formula, it gets updated automatically for each row accordingly.
2

Author Comment

ID: 41766566
Thanks much fellas...  It seems the Index Match did the trick... from Subodh Tiwari (Neeraj).  It was not a field to be aggregated..
0

LVL 36

Expert Comment

ID: 41766864
You're welcome Ayansane! Glad to help.
0

LVL 36

Expert Comment

ID: 41793218
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.