[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 39
  • Last Modified:

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

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
Ayansane
Asked:
Ayansane
  • 3
  • 2
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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))

Open in new window

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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
AyansaneAuthor Commented:
Thanks much fellas...  It seems the Index Match did the trick... from Subodh Tiwari (Neeraj).  It was not a field to be aggregated..
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Ayansane! Glad to help.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Asker preferred the chosen answer in his Post ID: 41766566.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now