Lookup "NAME" within range of cells and return "TEXT" few cells above the lookup-CodePudding

Using Excel2021

I'm trying to make a formula to solve my problem, if it is possible. I tried searching many formula online but it didn't fit in the formula I want to use on my file

CodePudding user response：

This formula is much simpler and doesn't requirde Vstack

I made the formula in small steps to make you understand better:

=MATCH(B24;'sheet 2'!$B$4:$H$4;0)

=MATCH(B24;'sheet 2'!$B$7:$H$7;0)

=IF(NOT(ISNA(MATCH(B24;'sheet 2'!$B$4:$H$4;0)));MATCH(B24;'sheet 2'!$B$4:$H$4;0);MATCH(B24;'sheet 2'!$B$7:$H$7;0))

=INDEX('sheet 2'!$B$2:$H$2;'sheet 1'!E35)

=INDEX('sheet 2'!$B$2:$H$2;IF(NOT(ISNA(MATCH(B24;'sheet 2'!$B$4:$H$4;0)));MATCH(B24;'sheet 2'!$B$4:$H$4;0);MATCH(B24;'sheet 2'!$B$7:$H$7;0)))

=IFNA(INDEX('sheet 2'!$B$2:$H$2;IF(NOT(ISNA(MATCH(B24;'sheet 2'!$B$4:$H$4;0)));MATCH(B24;'sheet 2'!$B$4:$H$4;0);MATCH(B24;'sheet 2'!$B$7:$H$7;0)));"not found")