Help with multi-dimensional lookups
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.
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.