Lookup Values across multiple tables

[Falko26]

All,

I'm trying to lookup and return the "Average: Total / Hr" value from a specific table based on the Crew Description.

Is there a way to do a type of lookup for that value?

I can VLOOKUP the crew description to get to the column I desire. Is there then a way to look down until first cell that says "Average:" and return the cell directly to the right of that or something?

 

[Falko26]

Okay, I have taken a closer look at your sample workbook, and I see that there's an issue. It looks like each cell in Column E that contains the label "Average:" actually has a leading and trailing space. So the formula needs to be adjusted. Give me a moment, and I'll post the amended formulas, as per your sample workbook.

Thank you I appreciate all the help!

 

[Domenic]

Okay, the names should be defined as follows . . .

Name:  BigNum
Refers to:  =MATCH(9.99999999999999E+307,'Labor Rates'!$F:$F)

Name:  TablesRange
Refers to:  ='Labor Rates'!$B$28:INDEX('Labor Rates'!$I:$I,'Labor Rates'!BigNum)

Then, the formula starting at D21, and copied down, should be as follows . . .

=INDEX(TablesRange,SMALL(IF(LEFT(TRIM(INDEX(TablesRange,0,4)),7)="Average",IF(ROW(TablesRange)-MIN(ROW(TablesRange))+1>MATCH([@[CREW DESCRIPTION]],INDEX(TablesRange,0,1),0),ROW(TablesRange)-MIN(ROW(TablesRange))+1)),1),5)

 

[Falko26]

Well that was incredible. Thank you so much for all of that! I never would have gotten that one to work. My knowledge of Excel isn't quite to that level.

Thanks again! We might chat soon on my next problem with this workbook

 

[Domenic]

You're very welcome, I'm glad I was able to help.

Cheers!