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?

 

[AhoyNC]

Maybe something like this with the CHOOSE function.

Book1
	A	B	C	D	E
1	ID	Crew	Rate
2	1	2 Man Roust	$ 65.00
3	2	3 Man Roust	$ 62.00
4	3	4 Man Roust
5	4	5 Man Roust
6
7
8	2 Man Roust
9	ID	Trade	Count	Rate	Total
10	1	Foreman
11	2	Laborer
12				Average	$ 65.00
13
14	3 Man Roust
15	ID	Trade	Count	Rate	Total
16	1	Foreman
17	2	Laborer
18				Average	$ 62.00
Sheet1

Cell Formulas
Range	Formula
C2:C3	C2	=CHOOSE(LEFT(B2,1),"",$E$12,$E$18)

 

[Falko26]

Thanks for the reply AhoyNC!

So this is on the right track. Now the only trick is the CHOOSE values selected can increase in number with more crews added below and change there row # location. If I add a crew line item to the main table above the individual crew tables below will shift down thus changing the row they are in. So I cant just select every average value outright I need some way to look for all averages.

If it helps every crew is in its own Excel Table.

I didn't know if there was a way to look for table based on crew name then return a specific cell in the "Total" row of the table? or something like that.

 

[Domenic]

It looks like all of the data is on one worksheet. So assuming that "Sheet1" contains the data, cell A1 contains the header "CREW RATES", cell A9 contains the header "2 MAN ROUST", and so on, try the following . . .

1) First define the following names (Ribbon >> Formulas >> Defined Names >> Name Manager). Change the sheet name accordingly.

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

Name:  TablesRange
Refers to:  ='Sheet1'!$A$9:INDEX('Sheet1'!$H:$H,BigNum)

2) Then enter the following formula in C3, confirm with CONTROL+SHIFT+ENTER, and copy down. (Actually, since you're using Office 365, there's no need to confirm the formula with CONTROL+SHIFT+ENTER. You only need to confirm with just ENTER.)

=INDEX(TablesRange,SMALL(IF(INDEX(TablesRange,0,4)="Average:",IF(ROW(TablesRange)-MIN(ROW(TablesRange))+1>MATCH(B3,INDEX(TablesRange,0,1),0),ROW(TablesRange)-MIN(ROW(TablesRange))+1)),1),5)

Note that the defined range will automatically adjust when you add other tables below the existing ones.

Hope this helps!

 

[Falko26]

Domenic Thank you for the detailed response!

Ok I'm adding more pictures so you can see rows, columns, & Sheet Names as I adapt your method.

I am getting pop up windows explorers each time I enter the formula into the cell that says "Update Values: Rates"? also when I enter the defined named ranges the first time a window pops up.

Results in a "#NAME?" Error.

Did I miss something? I am working with Excel Tables for everything...

BigNum:

TableRange:

Formula:

 

[Domenic]

Since your sheet name has a space, it needs to be within single quotes, like this . . .

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

I'll edit my post accordingly.

 

[Falko26]

Are these manually created tables? Or are they in fact tables created by going to Ribbon >> Insert tab >> Tables group >> Tables ?

Originally they were manually created, however as I add new crews I have a macro that simply copies the "2 Man Roust" Table and Pastes it underneath the last crew table. I then need to figure out how to format it from there to make it a "Blank Table". But that's another question.

Ultimately I'm trying to make a Crew Builder tool for my company so the construction manager can "Press Buttons" and create a rate calculator of sorts.

Sub BLC_CreateCrewTable()

Range("Table_2Roust[#All]").Copy Range("B" & Rows.Count).End(xlUp).Offset(4)
     
End Sub

 

[Falko26]

Are these manually created tables? Or are they in fact tables created by going to Ribbon >> Insert tab >> Tables group >> Tables ?

Maybe having the workbook might help.

Link:

Estimating Master- Jordan.xlsm

drive.google.com

 

[Domenic]

I see that those are in fact actual Tables. So I'll take another look when I get a chance. I may have an alternative solution. I'll have to see. If so, I'll post it.

At the same time, someone else here may have another solution. So stayed tuned.

 

[Domenic]

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.