Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
double match formula
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
double match formula
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 "Todd" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
double match formula
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 "Duke Carey" wrote: 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 "Todd" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
match formula - 2 excel files:#1 hasthis formula, 2nd has the Raw | Excel Worksheet Functions | |||
INDEX and MATCH in one formula... | Excel Worksheet Functions | |||
Help with formula statament with a MATCH. | Excel Worksheet Functions | |||
Match then lookup | Excel Worksheet Functions | |||
Match function...random search? | Excel Worksheet Functions |