• July 17, 2017
    Announcement: New site design for Microsoft Community

    In July, Microsoft will roll out the first of ongoing site improvements aimed to modernize Microsoft Community and help customers get the most out of their community experience.

    • During the roll out period, you may see the old or new site design depending on your location

    • We expect the roll out to finish by 31 July

    Note: Past private message conversations will not move to the new site design. Please save any private messages you would like to keep.

     Learn more about the upcoming site improvements in this thread.

    Thank you for being part of Microsoft Community!

 
Question
139 views

Help with multi-dimensional lookups

BKWSNX asked on

Hello,

 

I've got a worksheet ("WORKSHEET A") with ten columns. Each column ("COLUMN A1", "COLUMN A2", etc.) is titled with a unique number generated from an separate worksheet. Each title will dynamically change to a different number based on changes to this outside worksheet.

I've got another worksheet in the same workbook ("WORKSHEET B") with ten columns. Each column ("COLUMN B1", "COLUMN B2", etc.) is titled with a unique number that is static. Each column contains ten rows of static data with a unique number in every cell. There is one Defined Name ("NAMED ROW 1", "NAMED ROW 2", etc.) for each row in this worksheet. Each of the Defined Names has a range of one row and all ten columns.

 

Here's an example of what I would like to do:

 

I wish to have a cell in "WORKSHEET A" in "COLUMN A1" use the unique number stated in its own column title to look for the same unique number in the column titles of "WORKSHEET B." When it finds an exact match, in this example it's "COLUMN B6", I then want to tell it to find whatever number intersects "NAMED ROW 3" and "COLUMN B6" and display it back in that cell in "COLUMN A1".

I've done two-dimensional lookups before, but it's been a long while, so any help would be greatly appreciated.

Also, don't worry about if some of the title numbers in "WORKSHEET A" don't match any title numbers in "WORKSHEET B" as I'm going to slap on an IFERROR message in that rare circumstance.

1 person had this question

Abuse history


The answered status icon Answer

Hi,

 

In cell B5 of worksheet 2, use the following formula and copy down/right

 

=MIN('Worksheet #1'!E4,INDEX(_401_a_17_AND_404_l_Limits,1,MATCH('Worksheet #2 (2)'!B$1,'Worksheet #3'!$B$1:$L$1,0)))

 

Hope this helps.

Regards,

Ashish Mathur
www.ashishmathur.com
http://twitter.com/excelashish
Be the first person to mark this helpful

Abuse history


progress