Searching for a string and input value

[jalweber]

Hi Again All,

I have come across one final problem with my worksheet which is attached...

On the TakeOFF sheet, column C I need to have a seach for a string in the corresponding row of column B, depending on what that finds it needs to put in a different number value for each of the search strings.

I thought I had accomplished this through a VERY nasty nested IF statement. Basically it searches for the following terms and inputs the corresponding number.

Angle - 20
Box - 24
Channel - 40
Flat - 20
H-Beam - 40
I-Beam - 40
Pipe - 21
HSS - 20
Round Rod - 20

Any help at all is appreciated! I'm so close!

Thanks in advance

[NBVC]

Try:

=LOOKUP(9.999999E+307,SEARCH({"Angle","Box","Channel","Flat","H-Beam","HSS","I-Beam","Pipe","Round Rod"},B4),{20,24,20,20,20,40,40,21,20})

Note: the first array has to be in ascending alphabetic order.

Make sure the corresponding numbers are correct in second array.

[Marcol]

Try this, not as elegant as NBVC but it might help to explain why your formula failed

Please Login or Register  to view this content.

Drag/Fill down

You might be better off using lookup tables

[jalweber]

Again NBVC,

Thank you greatly for all of your help. I finally have exactly what I am looking for my material takeoffs!
Thanks so much!

Thanks for your response as well, Marcol!