double match formula

[Todd]

Hi, I am trying to match item numbers and unit of measure on one worksheet
to a master price list. My problem is that for each item number there are
several units of measure and I need to be sure and use the right price.

Worksheet 1 Price List

item unit of measure item unit of measure
price


I know how to do this with a sumproduct formula but this makes the workbook
calc. very slowy. So I am trying other ways.

=INDEX('price list'!$D:$D,MATCH($B2,'price list'!$A:$A,0),match(($c2,'price
list'!$b:$b,0)))) is the formula I am trying but am having problems with.


Thank you,


todd

 

[Duke Carey]

Can you concatenate the item # and unit of measure? If so, you can use an
array formula

=index(price range, match(item#&size, item#_range & size_range))

Commit it with Shift-Ctrl-Enter

 

[JMB]

Maybe use an exact match since the concatenated array may not be sorted?
=index(price range, match(item#&size, item#_range&size_range, 0))

Probably not the case, but just an FYI for the OP for future reference,
depending on the data, I've sometimes had issues w/the concatenated data not
being unique, such as
11 & 10 = 1110
1 & 110 = 1110

so I put a space in between the two fields
=index(price range, match(item#&" "&size, item#_range&" "&size_range, 0))

If the book still calculates slowly, you could try creating a helper column
on the price list worksheet and enter (lets say in column F)
=A1&B1
copy down

then
=INDEX('price list'!$D:$D,MATCH($B2&$c2,'price list'!$f:$f,0))
entered normally