Excel Formula help / Advice for a Dummy

Hi all,

Looking for any wizards out there who can help me build some formulas to try to achieve the below described goals for my attached excel file.

Any help, advice, or direction would be greatly appreciated!

Goals:

Return value in column H from "PRL1A PRL2A PART NUMBER DATA" sheet to cell L4 on "PANEL BUILDER" sheet.
Return value in column I from "PRL1A PRL2A PART NUMBER DATA" sheet to cell M4 on "PANEL BUILDER" sheet.
Return value in column J OR K from "PRL1A PRL2A PART NUMBER DATA" sheet to cell N4 on "PANEL BUILDER" sheet.

Variables:

Variables to determine returned values in column H & I ("PRL1A PRL2A PART NUMBER DATA" sheet) to be controlled by cell dropdowns A3 through G3 ("PANEL BUILDER" sheet). Equivalent Array data for these drop downs to reference are in the same order on the "PRL1A PRL2A PART NUMBER DATA" sheet columns A through G.

The variables to determine returned values in column J OR K ("PRL1A PRL2A PART NUMBER DATA" sheet) is controlled by the dropdown variable in H3 ("PANEL BUILDER" sheet). EG. If H3 is "SURFACE" data to be returned from column J ("PRL1A PRL2A PART NUMBER DATA") If H3 is "FLUSH" data to be returned from column K ("PRL1A PRL2A PART NUMBER DATA").

Hi Kmclean,

I am no excel pro by any measure, I have probably taken a very long way around a short solution but hey, its how my learning brain works.
I also couldn't get it to work with your original table setup, sorry about that.

Have a look at the attached, I think it works


In L4

=INDEX('PRL1A PRL2A PART NUMBER DATA'!$H:$H, MATCH($A$3&$B$3&$C$3&$D$3&$E$3&$F$3&$G$3,'PRL1A PRL2A PART NUMBER DATA'!$A:$A&$B:$B&$C:$C&$D:$D&$E:$E&$F:$F&$G:$G,0))

In M4

=INDEX('PRL1A PRL2A PART NUMBER DATA'!$I:$I,MATCH($A$3&$B$3&$C$3&$D$3&$E$3&$F$3&$G$3,'PRL1A PRL2A PART NUMBER DATA'!$A:$A&$B:$B&$C:$C&$D:$D&$E:$E&$F:$F&$G:$G,0))

In N4

=IF($H$3="SURFACE",INDEX('PRL1A PRL2A PART NUMBER DATA'!$J:$J,MATCH($A$3&$B$3&$C$3&$D$3&$E$3&$F$3&$G$3,'PRL1A PRL2A PART NUMBER DATA'!$A:$A&$B:$B&$C:$C&$D:$D&$E:$E&$F:$F&$G:$G, 0)),INDEX('PRL1A PRL2A PART NUMBER DATA'!$K:$K,MATCH(A3&B3&C3&D3&E3&F3&G3,'PRL1A PRL2A PART NUMBER DATA'!$A:$A&$B:$B&$C:$C&$D:$D&$E:$E&$F:$F&$G:$G,0)))