Hlookup?

**eramsva** · 09-10-2012, 09:40 AM

Hi experts!

I´m trying to sort some information, seen in the attachet example.

In Cells J3 to L5 I would like to identify the "commodities" used of each offer as per table 1.

I can use Hlookup with many nested IF statements, but is there an easier way?

Thanks for the help!

Martin

**MarkinTX** · 09-10-2012, 05:56 PM

In the attached spreadsheet I have added the custom Metal function

Function Metal(Srng As Range, HLook As Range, Oset As Integer)
Dim Cell As Range
Dim MCount As Integer
MCount = 0

For Each Cell In Srng
    If Application.WorksheetFunction.HLookup(Cell, HLook, Oset) = "Metal" Then
    MCount = MCount + 1
    End If
Next Cell

If MCount > 0 Then
Metal = "Yes"
Else
Metal = "No"
End If

End Function

In Cell J3 you will fine the formula "=metal(B3:H3,B13:H14,2)"
In the function the
first range is all of the items you want to search for
Second range is the "lookup" range
3rd item is the offset for the Hlookup command.

You can copy this function and rename it Rubber, plastic etc, and change the ="Metal" in the function to Rubber etc.

**Haseeb Avarakkan** · 09-10-2012, 07:58 PM

Also,

In J3 with CTRL+SHIFT+ENTER,

=IF(SUM(COUNTIF($B3:$H3,IF($B$14:$H$14=LEFT(J$2,FIND(" ",J$2)-1),$B$13:$H$13))),"Yes","No")

Then copy across & Down.