Hlookup? What else to do here.

PoloFacu

New Member
Joined
Jun 24, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
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
 

Attachments

  • Untitled.png
    Untitled.png
    15.6 KB · Views: 31

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
The formula I used was: =(((HLOOKUP("*Concrete*",B4:AE6,4,FALSE)-2)/12)*HLOOKUP("*Concrete*",B4:AE6,6,FALSE))
 
Upvote 0
Always a hassle to take data from a picture. Please consider using XL2BB in the future. Makes our life way easier here.
Book1
BCDEFGHIJK
1
2
3Fence Type 1Fence Type 2Fence Type 3Fence Type 4Fence Type 5
4Concrete44on concreteon soilon grasson concreteon asphalt
5404453344044
6no detailno detailno detailno detailno detail
72,1 L87L367L1647L52L
8
Sheet1
Cell Formulas
RangeFormula
C4:C5C4=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
FGHIJ
9TypePurposeHeightDetailsLiters
10Fence Type 1on concrete44no detail2,1 L
11Fence Type 2on soil53no detail87L
12Fence Type 3on grass34no detail367L
13Fence Type 4on concrete40no detail1647L
14Fence Type 5on asphalt44no detail52L
Sheet1
 
Upvote 0
Thanks for the reply! WIll do! Does having the cells merged, affect tth way the formulal will work?
 
Upvote 0
In general merged cells are a hassle, that's all. It won't affect the given formula. Except the used range is different.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,246
Messages
6,170,987
Members
452,373
Latest member
TimReeks

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top