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