Automated Price Look Up (How to do)

[B1313]

Hello,

I am trying to get values from a table that looks like this (simplified to a comprehensive and complete example):

Anybody have an idea of what function I can use if I have both the size and name of the item (ie L, Hat) to retrieve the price?

I am using a "table" (that is not made as a table) to store prices while I make another table that reports out stats and one section is based on retrieving prices based on some sizes & item names. So I want to automate the retrieving of the price.

Thanks

 

[oldbrewer]

if you put 8 against Tshirt ES S M L etc etc it is very easy with offset match

match down column A to find size M and match across row 2 to find sweater

=offset($a$2,match("M",a3:a12,0),match("Sweater",b2:e2,0))

 

[B1313]

Hey, thanks for the answer. It sparked a thought and I think I may have the ideal solution:

=INDEX($A$2:$E$12, MATCH(...), MATCH(...))

Where the MATCH arguments can be either static, mixed, or relative references just as long as they correlate between sizes and types. I also found a solution to making merged cell values show (via a custom function) from google.

Function MERGEVAL(merged_cell As Range)
    If merged_cell.Count = 1 Then
        MERGEVAL = merged_cell.MergeArea(1, 1)
    Else
        MERGEVAL = CVErr(xlErrRef)
    End If
End Function

So the final formula would be:

=MERGEVAL(INDEX($A$2:$E$12, MATCH(...), MATCH(...)))

So for an example (eg M Hat should return 10):

=MERGEVAL(INDEX($A$2:$E$12, MATCH("M", $A$3:$A$12, 0), MATCH("Hat", $A$2:$E$2, 0)))

 

[oldbrewer]

Great !

 

[B1313]

I made a small error in the example...here is the correction:

=MERGEVAL(INDEX($A$2:$E$12, MATCH("M", $A$2:$A$12, 0), MATCH("Hat", $A$2:$E$2, 0)))