+ Reply to Thread
Results 1 to 8 of 8

Index and Match on multiple similar tables

  1. #1
    Registered User
    Join Date
    08-06-2020
    Location
    India
    MS-Off Ver
    365
    Posts
    2

    Index and Match on multiple similar tables

    Hello,

    I need to use Index and Match function for this problem.
    Need to select the specific table using the "customer type" parameter and then a value inside that table by using "region" and "shipping" method. Parameters can be change using dropdown.

    Excel file Attached

    eg
    Attachment 689834

    Need to find Shipping per unit using the tables below
    Attachment 689835

    Thanks!!!
    Attached Files Attached Files
    Last edited by infinia; 08-06-2020 at 03:14 AM.

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    6,472

    Re: Index and Match on multiple similar tables

    Try INDEX(range,row index,collumn index) with:
    row index =MATCH("Customer type 30",B:B,0)+MATCH("West",$B$35:$B$41,0)
    column index=MATCH("Plane",$B$36:$F$36,0)

    Please Login or Register  to view this content.
    Try to replace text value with their actual address

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,281

    Re: Index and Match on multiple similar tables

    Welcome to the forum.

    INDEX MATCH MATCH with the AREA argument invoked will probably work for you.

    There are instructions at the top of the page explaining how to attach your sample workbook.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,281

    Re: Index and Match on multiple similar tables

    You have attached images that some members will not be able to see. I asked for a workbook - instructions at the top of the page.

  5. #5
    Valued Forum Contributor BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,059

    Re: Index and Match on multiple similar tables

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    or
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by BMV; 08-06-2020 at 03:22 AM.

  6. #6
    Registered User
    Join Date
    08-06-2020
    Location
    India
    MS-Off Ver
    365
    Posts
    2

    Re: Index and Match on multiple similar tables

    Hey,
    Thanks for the reply.

    But what if I change the value Customer Type 30 to Customer Type 20. This won't work I guess.
    I have attached the excel file.

    Thanks again!!

  7. #7
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    6,472

    Re: Index and Match on multiple similar tables

    It works for me:

    Please Login or Register  to view this content.
    Attached Files Attached Files

  8. #8
    Forum Guru
    Join Date
    05-24-2011
    Location
    Kuwait
    MS-Off Ver
    Office 365
    Posts
    2,199

    Re: Index and Match on multiple similar tables

    Hello,

    Assuming the columns (Rail, Truck, Plane & Ship) are in same order in each data group, Region can be in any order, if so use VLOOKUP like;

    =VLOOKUP(J5,INDEX(B:B,MATCH(J9,B:B,0)):F28,MATCH(J7,B5:F5,0),0)

    Change red highlighted to your last data row.
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 13
    Last Post: 04-22-2020, 06:44 AM
  2. [SOLVED] INDEX / MATCH using multiple tables depending on values?
    By nostrum in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 06-21-2019, 05:15 AM
  3. Index match with multiple similar columns/rows?
    By sh4d0w1ink in forum Excel General
    Replies: 3
    Last Post: 06-05-2019, 03:27 PM
  4. How to use INDEX MATCH in unison with AVG, MIN, MAX etc across multiple tables?
    By TheBananaGuy in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-29-2018, 12:48 AM
  5. [SOLVED] INDEX MATCH MATCH with multiple layered tables
    By acenewbie in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-07-2018, 11:51 AM
  6. Using index match to find terms on multiple tables?
    By tsiguy96 in forum Excel General
    Replies: 3
    Last Post: 08-12-2015, 05:25 AM
  7. Need data cross-referenced in multiple tables: VLOOKUP vs. Match vs. Index
    By James McMurray in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-05-2008, 02:53 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts