| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Offset & Match Formula Shows Duplicates
I use this array formula {=IF(F37="","",OFFSET('Priced
BOM'!$AD$3,MATCH(F37,'Priced BOM'!$AD$3:$AD$164,0)-1,-19,1,1))} and what it does is find a part number (-19) baseed on the cost of an item (F & AD). If the cost of two or more items is the same it always lists the first part. Is there a way to to have this formula check to see if the part is on the row above and if so have it list the next part that matches the cost? Thank you for your help and Happy Holidays Joe |
|
#2
|
|||
|
|||
|
Hi
first: no need to array enter this formula at all. Should do as non-aaray formula One way to get multiple returns is the following array formula: =IF(F37="","",INDEX('Priced BOM'!$K$3:$K$164,SMALL(IF(F37='Priced BOM'!$AD$3:$AD$164,ROW('Priced BOM'!$AD$3:$AD$164)),ROW(1:1)))) and copy this formula down. Note: not completely robust due to the usage of ROW(1:1). If you for example enter this formula in row 37 you could change the formula to: =IF(F37="","",INDEX('Priced BOM'!$K$3:$K$164,SMALL(IF(F37='Priced BOM'!$AD$3:$AD$164,ROW('Priced BOM'!$AD$3:$AD$164)),ROW()-ROW($F$37)+1))) -- Regards Frank Kabel Frankfurt, Germany "Joe Gieder" schrieb im Newsbeitrag ... I use this array formula {=IF(F37="","",OFFSET('Priced BOM'!$AD$3,MATCH(F37,'Priced BOM'!$AD$3:$AD$164,0)-1,-19,1,1))} and what it does is find a part number (-19) baseed on the cost of an item (F & AD). If the cost of two or more items is the same it always lists the first part. Is there a way to to have this formula check to see if the part is on the row above and if so have it list the next part that matches the cost? Thank you for your help and Happy Holidays Joe |
|
#3
|
|||
|
|||
|
Hi Frank,
"Frank Kabel" wrote: Hi first: no need to array enter this formula at all. Should do as non-aaray formula One way to get multiple returns is the following array formula: =IF(F37="","",INDEX('Priced BOM'!$K$3:$K$164,SMALL(IF(F37='Priced BOM'!$AD$3:$AD$164,ROW('Priced BOM'!$AD$3:$AD$164)),ROW(1:1)))) and copy this formula down. Note: not completely robust due to the usage of ROW(1:1). If you for example enter this formula in row 37 you could change the formula to: =IF(F37="","",INDEX('Priced BOM'!$K$3:$K$164,SMALL(IF(F37='Priced BOM'!$AD$3:$AD$164,ROW('Priced BOM'!$AD$3:$AD$164)),ROW()-ROW($F$37)+1))) -- Regards Frank Kabel Frankfurt, Germany "Joe Gieder" schrieb im Newsbeitrag ... I use this array formula {=IF(F37="","",OFFSET('Priced BOM'!$AD$3,MATCH(F37,'Priced BOM'!$AD$3:$AD$164,0)-1,-19,1,1))} and what it does is find a part number (-19) baseed on the cost of an item (F & AD). If the cost of two or more items is the same it always lists the first part. Is there a way to to have this formula check to see if the part is on the row above and if so have it list the next part that matches the cost? Thank you for your help and Happy Holidays Joe |
|
#4
|
|||
|
|||
|
Hi Frank,
Thank you for the formula. Maybe I did something wrong, when I copied it I came out with the wrong result, the result I obtained was two rows below the correct one and then when I copied the formula down I ended up with a #REF error because I hit the end of the Priced BOM spreadsheet (there should be 35 results). What could I have done wrong, I copied it exactly like you wrote it? The cell I want to use as the reference is F37 which is the cost and it looks in 'Priced BOM' AD3:AD164 I'm trying to retrieve the part number which is in 'Priced BOM' K3:K164. I'm sure there's something I didn't follow right could you please help. Thanks Joe "Frank Kabel" wrote: Hi first: no need to array enter this formula at all. Should do as non-aaray formula One way to get multiple returns is the following array formula: =IF(F37="","",INDEX('Priced BOM'!$K$3:$K$164,SMALL(IF(F37='Priced BOM'!$AD$3:$AD$164,ROW('Priced BOM'!$AD$3:$AD$164)),ROW(1:1)))) and copy this formula down. Note: not completely robust due to the usage of ROW(1:1). If you for example enter this formula in row 37 you could change the formula to: =IF(F37="","",INDEX('Priced BOM'!$K$3:$K$164,SMALL(IF(F37='Priced BOM'!$AD$3:$AD$164,ROW('Priced BOM'!$AD$3:$AD$164)),ROW()-ROW($F$37)+1))) -- Regards Frank Kabel Frankfurt, Germany "Joe Gieder" schrieb im Newsbeitrag ... I use this array formula {=IF(F37="","",OFFSET('Priced BOM'!$AD$3,MATCH(F37,'Priced BOM'!$AD$3:$AD$164,0)-1,-19,1,1))} and what it does is find a part number (-19) baseed on the cost of an item (F & AD). If the cost of two or more items is the same it always lists the first part. Is there a way to to have this formula check to see if the part is on the row above and if so have it list the next part that matches the cost? Thank you for your help and Happy Holidays Joe |
|
#5
|
|||
|
|||
|
Hi
yes, my fault. Make this: =IF(F37="","",INDEX('Priced BOM'!$K$3:$K$164,SMALL(IF(F37='Priced BOM'!$AD$3:$AD$164,ROW('Priced BOM'!$AD$3:$AD$164)),ROW(1:1))-2)) -- Regards Frank Kabel Frankfurt, Germany "Joe Gieder" schrieb im Newsbeitrag ... Hi Frank, "Frank Kabel" wrote: Hi first: no need to array enter this formula at all. Should do as non-aaray formula One way to get multiple returns is the following array formula: =IF(F37="","",INDEX('Priced BOM'!$K$3:$K$164,SMALL(IF(F37='Priced BOM'!$AD$3:$AD$164,ROW('Priced BOM'!$AD$3:$AD$164)),ROW(1:1)))) and copy this formula down. Note: not completely robust due to the usage of ROW(1:1). If you for example enter this formula in row 37 you could change the formula to: =IF(F37="","",INDEX('Priced BOM'!$K$3:$K$164,SMALL(IF(F37='Priced BOM'!$AD$3:$AD$164,ROW('Priced BOM'!$AD$3:$AD$164)),ROW()-ROW($F$37)+1))) -- Regards Frank Kabel Frankfurt, Germany "Joe Gieder" schrieb im Newsbeitrag ... I use this array formula {=IF(F37="","",OFFSET('Priced BOM'!$AD$3,MATCH(F37,'Priced BOM'!$AD$3:$AD$164,0)-1,-19,1,1))} and what it does is find a part number (-19) baseed on the cost of an item (F & AD). If the cost of two or more items is the same it always lists the first part. Is there a way to to have this formula check to see if the part is on the row above and if so have it list the next part that matches the cost? Thank you for your help and Happy Holidays Joe |
|
#6
|
|||
|
|||
|
I copied and pasted and I get different part numbers (the same results as
with my first formula) but I still get duplicate part numbers if the cost is the same for two different parts. Sorry for the trouble. Thanks Joe "Frank Kabel" wrote: Hi yes, my fault. Make this: =IF(F37="","",INDEX('Priced BOM'!$K$3:$K$164,SMALL(IF(F37='Priced BOM'!$AD$3:$AD$164,ROW('Priced BOM'!$AD$3:$AD$164)),ROW(1:1))-2)) -- Regards Frank Kabel Frankfurt, Germany "Joe Gieder" schrieb im Newsbeitrag ... Hi Frank, "Frank Kabel" wrote: Hi first: no need to array enter this formula at all. Should do as non-aaray formula One way to get multiple returns is the following array formula: =IF(F37="","",INDEX('Priced BOM'!$K$3:$K$164,SMALL(IF(F37='Priced BOM'!$AD$3:$AD$164,ROW('Priced BOM'!$AD$3:$AD$164)),ROW(1:1)))) and copy this formula down. Note: not completely robust due to the usage of ROW(1:1). If you for example enter this formula in row 37 you could change the formula to: =IF(F37="","",INDEX('Priced BOM'!$K$3:$K$164,SMALL(IF(F37='Priced BOM'!$AD$3:$AD$164,ROW('Priced BOM'!$AD$3:$AD$164)),ROW()-ROW($F$37)+1))) -- Regards Frank Kabel Frankfurt, Germany "Joe Gieder" schrieb im Newsbeitrag ... I use this array formula {=IF(F37="","",OFFSET('Priced BOM'!$AD$3,MATCH(F37,'Priced BOM'!$AD$3:$AD$164,0)-1,-19,1,1))} and what it does is find a part number (-19) baseed on the cost of an item (F & AD). If the cost of two or more items is the same it always lists the first part. Is there a way to to have this formula check to see if the part is on the row above and if so have it list the next part that matches the cost? Thank you for your help and Happy Holidays Joe |
|
#7
|
|||
|
|||
|
Hi
what is the exact formula you have used and what are your result -- Regards Frank Kabel Frankfurt, Germany "Joe Gieder" schrieb im Newsbeitrag ... I copied and pasted and I get different part numbers (the same results as with my first formula) but I still get duplicate part numbers if the cost is the same for two different parts. Sorry for the trouble. Thanks Joe "Frank Kabel" wrote: Hi yes, my fault. Make this: =IF(F37="","",INDEX('Priced BOM'!$K$3:$K$164,SMALL(IF(F37='Priced BOM'!$AD$3:$AD$164,ROW('Priced BOM'!$AD$3:$AD$164)),ROW(1:1))-2)) -- Regards Frank Kabel Frankfurt, Germany "Joe Gieder" schrieb im Newsbeitrag ... Hi Frank, "Frank Kabel" wrote: Hi first: no need to array enter this formula at all. Should do as non-aaray formula One way to get multiple returns is the following array formula: =IF(F37="","",INDEX('Priced BOM'!$K$3:$K$164,SMALL(IF(F37='Priced BOM'!$AD$3:$AD$164,ROW('Priced BOM'!$AD$3:$AD$164)),ROW(1:1)))) and copy this formula down. Note: not completely robust due to the usage of ROW(1:1). If you for example enter this formula in row 37 you could change the formula to: =IF(F37="","",INDEX('Priced BOM'!$K$3:$K$164,SMALL(IF(F37='Priced BOM'!$AD$3:$AD$164,ROW('Priced BOM'!$AD$3:$AD$164)),ROW()-ROW($F$37)+1))) -- Regards Frank Kabel Frankfurt, Germany "Joe Gieder" schrieb im Newsbeitrag ... I use this array formula {=IF(F37="","",OFFSET('Priced BOM'!$AD$3,MATCH(F37,'Priced BOM'!$AD$3:$AD$164,0)-1,-19,1,1))} and what it does is find a part number (-19) baseed on the cost of an item (F & AD). If the cost of two or more items is the same it always lists the first part. Is there a way to to have this formula check to see if the part is on the row above and if so have it list the next part that matches the cost? Thank you for your help and Happy Holidays Joe |
|
#8
|
|||
|
|||
|
The formulas I have tried are as entered starting in C37:
=IF(F37="","",OFFSET('Priced BOM'!$AD$3,MATCH(F37,'Priced BOM'!$AD$3:$AD$164,0)-1,-19,1,1)) C37 F37 C37 Result SB B32281006-102 $182,732.82 B32281006-102 correct B32281006-101 $182,732.37 B32281006-101 correct B32281006-103 $56,851.23 B32281006-103 correct B32281006-104 $56,391.69 B32281006-104 correct B32281006-109 $29,420.28 B32281006-109 correct B32281006-109 $29,420.28 B32281006-110 wrong 368659-3 $28,218.75 368659-3 correct 368659-3 $28,218.75 368659-4 wrong 363576-6 $23,786.97 363576-6 correct 391066-1 $15,014.70 391066-1 correct 4085962-1 $11,669.16 4085962-1 correct 4085962-1 $11,669.16 4085962-2 wrong and others.. And I used your formula: =IF(F37="","",INDEX('Priced BOM'!$K$3:$K$164,SMALL(IF(F37='Priced BOM'!$AD$3:$AD$164,ROW('Priced BOM'!$AD$3:$AD$164)),ROW(1:1))-2)) And received the same results as above. Thank you and Best Regards Joe "Frank Kabel" wrote: Hi what is the exact formula you have used and what are your result -- Regards Frank Kabel Frankfurt, Germany "Joe Gieder" schrieb im Newsbeitrag ... I copied and pasted and I get different part numbers (the same results as with my first formula) but I still get duplicate part numbers if the cost is the same for two different parts. Sorry for the trouble. Thanks Joe "Frank Kabel" wrote: Hi yes, my fault. Make this: =IF(F37="","",INDEX('Priced BOM'!$K$3:$K$164,SMALL(IF(F37='Priced BOM'!$AD$3:$AD$164,ROW('Priced BOM'!$AD$3:$AD$164)),ROW(1:1))-2)) -- Regards Frank Kabel Frankfurt, Germany "Joe Gieder" schrieb im Newsbeitrag ... Hi Frank, "Frank Kabel" wrote: Hi first: no need to array enter this formula at all. Should do as non-aaray formula One way to get multiple returns is the following array formula: =IF(F37="","",INDEX('Priced BOM'!$K$3:$K$164,SMALL(IF(F37='Priced BOM'!$AD$3:$AD$164,ROW('Priced BOM'!$AD$3:$AD$164)),ROW(1:1)))) and copy this formula down. Note: not completely robust due to the usage of ROW(1:1). If you for example enter this formula in row 37 you could change the formula to: =IF(F37="","",INDEX('Priced BOM'!$K$3:$K$164,SMALL(IF(F37='Priced BOM'!$AD$3:$AD$164,ROW('Priced BOM'!$AD$3:$AD$164)),ROW()-ROW($F$37)+1))) -- Regards Frank Kabel Frankfurt, Germany "Joe Gieder" schrieb im Newsbeitrag ... I use this array formula {=IF(F37="","",OFFSET('Priced BOM'!$AD$3,MATCH(F37,'Priced BOM'!$AD$3:$AD$164,0)-1,-19,1,1))} and what it does is find a part number (-19) baseed on the cost of an item (F & AD). If the cost of two or more items is the same it always lists the first part. Is there a way to to have this formula check to see if the part is on the row above and if so have it list the next part that matches the cost? Thank you for your help and Happy Holidays Joe |
|
#9
|
|||
|
|||
|
Hi
have you entered my formulas as array formula? -- Regards Frank Kabel Frankfurt, Germany "Joe Gieder" schrieb im Newsbeitrag ... The formulas I have tried are as entered starting in C37: =IF(F37="","",OFFSET('Priced BOM'!$AD$3,MATCH(F37,'Priced BOM'!$AD$3:$AD$164,0)-1,-19,1,1)) C37 F37 C37 Result SB B32281006-102 $182,732.82 B32281006-102 correct B32281006-101 $182,732.37 B32281006-101 correct B32281006-103 $56,851.23 B32281006-103 correct B32281006-104 $56,391.69 B32281006-104 correct B32281006-109 $29,420.28 B32281006-109 correct B32281006-109 $29,420.28 B32281006-110 wrong 368659-3 $28,218.75 368659-3 correct 368659-3 $28,218.75 368659-4 wrong 363576-6 $23,786.97 363576-6 correct 391066-1 $15,014.70 391066-1 correct 4085962-1 $11,669.16 4085962-1 correct 4085962-1 $11,669.16 4085962-2 wrong and others.. And I used your formula: =IF(F37="","",INDEX('Priced BOM'!$K$3:$K$164,SMALL(IF(F37='Priced BOM'!$AD$3:$AD$164,ROW('Priced BOM'!$AD$3:$AD$164)),ROW(1:1))-2)) And received the same results as above. Thank you and Best Regards Joe |
|
#10
|
|||
|
|||
|
I did.
"Frank Kabel" wrote: Hi have you entered my formulas as array formula? -- Regards Frank Kabel Frankfurt, Germany "Joe Gieder" schrieb im Newsbeitrag ... The formulas I have tried are as entered starting in C37: =IF(F37="","",OFFSET('Priced BOM'!$AD$3,MATCH(F37,'Priced BOM'!$AD$3:$AD$164,0)-1,-19,1,1)) C37 F37 C37 Result SB B32281006-102 $182,732.82 B32281006-102 correct B32281006-101 $182,732.37 B32281006-101 correct B32281006-103 $56,851.23 B32281006-103 correct B32281006-104 $56,391.69 B32281006-104 correct B32281006-109 $29,420.28 B32281006-109 correct B32281006-109 $29,420.28 B32281006-110 wrong 368659-3 $28,218.75 368659-3 correct 368659-3 $28,218.75 368659-4 wrong 363576-6 $23,786.97 363576-6 correct 391066-1 $15,014.70 391066-1 correct 4085962-1 $11,669.16 4085962-1 correct 4085962-1 $11,669.16 4085962-2 wrong and others.. And I used your formula: =IF(F37="","",INDEX('Priced BOM'!$K$3:$K$164,SMALL(IF(F37='Priced BOM'!$AD$3:$AD$164,ROW('Priced BOM'!$AD$3:$AD$164)),ROW(1:1))-2)) And received the same results as above. Thank you and Best Regards Joe |
|
#11
|
|||
|
|||
|
Hi
if you like email me your non working sample sheet -- Regards Frank Kabel Frankfurt, Germany "Joe Gieder" schrieb im Newsbeitrag ... I did. "Frank Kabel" wrote: Hi have you entered my formulas as array formula? -- Regards Frank Kabel Frankfurt, Germany "Joe Gieder" schrieb im Newsbeitrag ... The formulas I have tried are as entered starting in C37: =IF(F37="","",OFFSET('Priced BOM'!$AD$3,MATCH(F37,'Priced BOM'!$AD$3:$AD$164,0)-1,-19,1,1)) C37 F37 C37 Result SB B32281006-102 $182,732.82 B32281006-102 correct B32281006-101 $182,732.37 B32281006-101 correct B32281006-103 $56,851.23 B32281006-103 correct B32281006-104 $56,391.69 B32281006-104 correct B32281006-109 $29,420.28 B32281006-109 correct B32281006-109 $29,420.28 B32281006-110 wrong 368659-3 $28,218.75 368659-3 correct 368659-3 $28,218.75 368659-4 wrong 363576-6 $23,786.97 363576-6 correct 391066-1 $15,014.70 391066-1 correct 4085962-1 $11,669.16 4085962-1 correct 4085962-1 $11,669.16 4085962-2 wrong and others.. And I used your formula: =IF(F37="","",INDEX('Priced BOM'!$K$3:$K$164,SMALL(IF(F37='Priced BOM'!$AD$3:$AD$164,ROW('Priced BOM'!$AD$3:$AD$164)),ROW(1:1))-2)) And received the same results as above. Thank you and Best Regards Joe |
|
#12
|
|||
|
|||
|
I did. However; I get a #NUM! error unless I put a $ in front of the first 1
in ROW(1:1)-2 and then the formul goes $1:1 $1:2 and so on. "Joe Gieder" wrote: The formulas I have tried are as entered starting in C37: =IF(F37="","",OFFSET('Priced BOM'!$AD$3,MATCH(F37,'Priced BOM'!$AD$3:$AD$164,0)-1,-19,1,1)) C37 F37 C37 Result SB B32281006-102 $182,732.82 B32281006-102 correct B32281006-101 $182,732.37 B32281006-101 correct B32281006-103 $56,851.23 B32281006-103 correct B32281006-104 $56,391.69 B32281006-104 correct B32281006-109 $29,420.28 B32281006-109 correct B32281006-109 $29,420.28 B32281006-110 wrong 368659-3 $28,218.75 368659-3 correct 368659-3 $28,218.75 368659-4 wrong 363576-6 $23,786.97 363576-6 correct 391066-1 $15,014.70 391066-1 correct 4085962-1 $11,669.16 4085962-1 correct 4085962-1 $11,669.16 4085962-2 wrong and others.. And I used your formula: =IF(F37="","",INDEX('Priced BOM'!$K$3:$K$164,SMALL(IF(F37='Priced BOM'!$AD$3:$AD$164,ROW('Priced BOM'!$AD$3:$AD$164)),ROW(1:1))-2)) And received the same results as above. Thank you and Best Regards Joe "Frank Kabel" wrote: Hi what is the exact formula you have used and what are your result -- Regards Frank Kabel Frankfurt, Germany "Joe Gieder" schrieb im Newsbeitrag ... I copied and pasted and I get different part numbers (the same results as with my first formula) but I still get duplicate part numbers if the cost is the same for two different parts. Sorry for the trouble. Thanks Joe "Frank Kabel" wrote: Hi yes, my fault. Make this: =IF(F37="","",INDEX('Priced BOM'!$K$3:$K$164,SMALL(IF(F37='Priced BOM'!$AD$3:$AD$164,ROW('Priced BOM'!$AD$3:$AD$164)),ROW(1:1))-2)) -- Regards Frank Kabel Frankfurt, Germany "Joe Gieder" schrieb im Newsbeitrag ... Hi Frank, "Frank Kabel" wrote: Hi first: no need to array enter this formula at all. Should do as non-aaray formula One way to get multiple returns is the following array formula: =IF(F37="","",INDEX('Priced BOM'!$K$3:$K$164,SMALL(IF(F37='Priced BOM'!$AD$3:$AD$164,ROW('Priced BOM'!$AD$3:$AD$164)),ROW(1:1)))) and copy this formula down. Note: not completely robust due to the usage of ROW(1:1). If you for example enter this formula in row 37 you could change the formula to: =IF(F37="","",INDEX('Priced BOM'!$K$3:$K$164,SMALL(IF(F37='Priced BOM'!$AD$3:$AD$164,ROW('Priced BOM'!$AD$3:$AD$164)),ROW()-ROW($F$37)+1))) -- Regards Frank Kabel Frankfurt, Germany "Joe Gieder" schrieb im Newsbeitrag ... I use this array formula {=IF(F37="","",OFFSET('Priced BOM'!$AD$3,MATCH(F37,'Priced BOM'!$AD$3:$AD$164,0)-1,-19,1,1))} and what it does is find a part number (-19) baseed on the cost of an item (F & AD). If the cost of two or more items is the same it always lists the first part. Is there a way to to have this formula check to see if the part is on the row above and if so have it list the next part that matches the cost? Thank you for your help and Happy Holidays Joe |
|
#13
|
|||
|
|||
|
I will thank you. Were should I send it to?
Joe "Frank Kabel" wrote: Hi if you like email me your non working sample sheet -- Regards Frank Kabel Frankfurt, Germany "Joe Gieder" schrieb im Newsbeitrag ... I did. "Frank Kabel" wrote: Hi have you entered my formulas as array formula? -- Regards Frank Kabel Frankfurt, Germany "Joe Gieder" schrieb im Newsbeitrag ... The formulas I have tried are as entered starting in C37: =IF(F37="","",OFFSET('Priced BOM'!$AD$3,MATCH(F37,'Priced BOM'!$AD$3:$AD$164,0)-1,-19,1,1)) C37 F37 C37 Result SB B32281006-102 $182,732.82 B32281006-102 correct B32281006-101 $182,732.37 B32281006-101 correct B32281006-103 $56,851.23 B32281006-103 correct B32281006-104 $56,391.69 B32281006-104 correct B32281006-109 $29,420.28 B32281006-109 correct B32281006-109 $29,420.28 B32281006-110 wrong 368659-3 $28,218.75 368659-3 correct 368659-3 $28,218.75 368659-4 wrong 363576-6 $23,786.97 363576-6 correct 391066-1 $15,014.70 391066-1 correct 4085962-1 $11,669.16 4085962-1 correct 4085962-1 $11,669.16 4085962-2 wrong and others.. And I used your formula: =IF(F37="","",INDEX('Priced BOM'!$K$3:$K$164,SMALL(IF(F37='Priced BOM'!$AD$3:$AD$164,ROW('Priced BOM'!$AD$3:$AD$164)),ROW(1:1))-2)) And received the same results as above. Thank you and Best Regards Joe |
|
#14
|
|||
|
|||
|
frank[dot]kabel[at]freenet[dot]de
-- Regards Frank Kabel Frankfurt, Germany "Joe Gieder" schrieb im Newsbeitrag ... I will thank you. Were should I send it to? Joe "Frank Kabel" wrote: Hi if you like email me your non working sample sheet -- Regards Frank Kabel Frankfurt, Germany "Joe Gieder" schrieb im Newsbeitrag ... I did. "Frank Kabel" wrote: Hi have you entered my formulas as array formula? -- Regards Frank Kabel Frankfurt, Germany "Joe Gieder" schrieb im Newsbeitrag ... The formulas I have tried are as entered starting in C37: =IF(F37="","",OFFSET('Priced BOM'!$AD$3,MATCH(F37,'Priced BOM'!$AD$3:$AD$164,0)-1,-19,1,1)) C37 F37 C37 Result SB B32281006-102 $182,732.82 B32281006-102 correct B32281006-101 $182,732.37 B32281006-101 correct B32281006-103 $56,851.23 B32281006-103 correct B32281006-104 $56,391.69 B32281006-104 correct B32281006-109 $29,420.28 B32281006-109 correct B32281006-109 $29,420.28 B32281006-110 wrong 368659-3 $28,218.75 368659-3 correct 368659-3 $28,218.75 368659-4 wrong 363576-6 $23,786.97 363576-6 correct 391066-1 $15,014.70 391066-1 correct 4085962-1 $11,669.16 4085962-1 correct 4085962-1 $11,669.16 4085962-2 wrong and others.. And I used your formula: =IF(F37="","",INDEX('Priced BOM'!$K$3:$K$164,SMALL(IF(F37='Priced BOM'!$AD$3:$AD$164,ROW('Priced BOM'!$AD$3:$AD$164)),ROW(1:1))-2)) And received the same results as above. Thank you and Best Regards Joe |
|
#15
|
|||
|
|||
|
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Offset, indirect, match function limitation on linked worksheets. | Excel Worksheet Functions | |||
| Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions | |||
| Why does the formula result show a number but the screen shows ze. | Excel Worksheet Functions | |||
| Excel 2003 - Formula result shows as 0:00 | Excel Worksheet Functions | |||
| how to build a formula to match numbers in 2 columns with the equ. | Excel Worksheet Functions | |||
Linear Mode
