Hlookup? What else to do here.

[PoloFacu]

Hi,

So I am having some trouble figuring out how to summarize the square feet of the fences being applied to concrete, grass. asphalt and soil.

What I am looking to achieve is for excel to look for "*concrete*" in rows B4:AE6, and return the values in row 7 (the height). In this case that would be 44 and 40.

I have tried using hlookup but it only returns on value but I need it to look for all the match part of the text.

Help please

 

[PoloFacu]

The formula I used was: =(((HLOOKUP("*Concrete*",B4:AE6,4,FALSE)-2)/12)*HLOOKUP("*Concrete*",B4:AE6,6,FALSE))

 

[GraH]

Always a hassle to take data from a picture. Please consider using XL2BB in the future. Makes our life way easier here.

Book1
	B	C	D	E	F	G	H	I	J	K
1
2
3					Fence Type 1	Fence Type 2	Fence Type 3	Fence Type 4	Fence Type 5
4	Concrete	44			on concrete	on soil	on grass	on concrete	on asphalt
5		40			44	53	34	40	44
6					no detail	no detail	no detail	no detail	no detail
7					2,1 L	87L	367L	1647L	52L
8
Sheet1

Cell Formulas
Range	Formula
C4:C5	C4	=INDEX($F$5:$J$5,,AGGREGATE(15,6,(COLUMN($F$3:$J$3)-COLUMN($E$3))/(SEARCH("*concrete*",$F$4:$J$4)^0),ROW(C1)))

As a suggestion: do not use merge cells. Though I can't really tell from your picture you do so.
Why not use a vertical table? Would make any lookup a bit easier.

Book1
	F	G	H	I	J
9	Type	Purpose	Height	Details	Liters
10	Fence Type 1	on concrete	44	no detail	2,1 L
11	Fence Type 2	on soil	53	no detail	87L
12	Fence Type 3	on grass	34	no detail	367L
13	Fence Type 4	on concrete	40	no detail	1647L
14	Fence Type 5	on asphalt	44	no detail	52L
Sheet1

 

[PoloFacu]

Thanks for the reply! WIll do! Does having the cells merged, affect tth way the formulal will work?

 

[GraH]

In general merged cells are a hassle, that's all. It won't affect the given formula. Except the used range is different.

 

[PoloFacu]

Costing W.I.P.P.P.xlsx
	R					W	X					AC	AD					AI	AJ					AO	AP					AU	AV					BA
1	1						2						3						4						5						6
2	Chain link fence installed on concrete						Wood fence installed on soil						Plastic Fence installed on grass						Wood fence installed on concrete						Custom fence installed on asphalt
3
4
6	44.'' H						53.'' H						34.'' H						40.'' H						44.'' H						.''
7	No Detail						No Detail						No Detail						No Detail						No Detail						0
8	2141 LF						87 LF						367 LF						1647 LF						52 LF						0 LF
Production Cost

 

[PoloFacu]

This is what I actually have. I need it to look at cells R2:AU4 and find the cells the contain "concrete" and return the values 44 and 40. And if possible, provide a sumproduct by multiplying it the LF two cells below it.