Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Question regarding formula
First, thank you in advance for your help and looking at message.
I use this array formula: =IF(F37="","",OFFSET('Priced BOM'!$T$3,MATCH(F37,'Priced BOM'!$AD$3:$AD$1270,0)-1,-9,1,1)) and it works fine with one exception. The formula is copied down many rows and what it does is takes the total cost of a part (F37) and matches it to $AD$3:$AD$1270. When the result is found it then copies the part number, the problem is that if two different part numbers exist with the same cost it will only show the first entry (I know this is how match works, finding the first occurance and then proceeding to the next row). Is there a way to say if the row above is the same value skip to the next value with the same cost? Thanks in advance for the help. Joe |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Question regarding formula
Maybe...
=IF(F37="","",OFFSET('Priced BOM'!$T$3,MATCH(2,1/('Priced BOM'!$AD$3:$AD$1270=F37))-1,-9,1,1)) ....which will match the last occurrence. Although, I'd use the following formula instead... =IF(F37<"",INDEX('Priced BOM'!$K$3:$K$1270,MATCH(2,1/('Priced BOM'!$AD$3:$AD$1270=F37))),"") ....because it excludes the OFFSET function which is volatile. Note that both formulas need to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , "Joe Gieder" wrote: First, thank you in advance for your help and looking at message. I use this array formula: =IF(F37="","",OFFSET('Priced BOM'!$T$3,MATCH(F37,'Priced BOM'!$AD$3:$AD$1270,0)-1,-9,1,1)) and it works fine with one exception. The formula is copied down many rows and what it does is takes the total cost of a part (F37) and matches it to $AD$3:$AD$1270. When the result is found it then copies the part number, the problem is that if two different part numbers exist with the same cost it will only show the first entry (I know this is how match works, finding the first occurance and then proceeding to the next row). Is there a way to say if the row above is the same value skip to the next value with the same cost? Thanks in advance for the help. Joe |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Question regarding formula
Hi!
This seems backwards to me! But what do I know! You lookup the price to get the part number? Why don't you lookup the part number to get the price? A couple of tidbits: That formula does not need to be array entered. it works fine with one exception Then that means it DOESN'T work! <g A better, non-volatile formula to use: =IF(F37="","",INDEX($K$3:$K$1270,MATCH(F37,$AD$3:$ AD$1270,0))) Is there a way to say if the row above is the same value skip to the next value with the same cost? If you have multiple instances of a price how do you know which part number you want returned? Are there any instances where 5 or 10 items may have the same price? How do you know which corresponding part you want? Biff "Joe Gieder" wrote in message ... First, thank you in advance for your help and looking at message. I use this array formula: =IF(F37="","",OFFSET('Priced BOM'!$T$3,MATCH(F37,'Priced BOM'!$AD$3:$AD$1270,0)-1,-9,1,1)) and it works fine with one exception. The formula is copied down many rows and what it does is takes the total cost of a part (F37) and matches it to $AD$3:$AD$1270. When the result is found it then copies the part number, the problem is that if two different part numbers exist with the same cost it will only show the first entry (I know this is how match works, finding the first occurance and then proceeding to the next row). Is there a way to say if the row above is the same value skip to the next value with the same cost? Thanks in advance for the help. Joe |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Question regarding formula
Hi Biff,
What I'm doing is listing the top 10 and 35 most expensive parts. I know this can be done with autofilter and copying to the spreadsheet but I needfor this to be formula driven. The formula does work because I don't very often have the same price for several different part numbers but when I do I get the first part number all the time. With the information I use many part numbers can have the same cost but I want to be able to show each and every occurance, it causes confusion when one part number shows up multiple time with the same dollar value. "Biff" wrote: Hi! This seems backwards to me! But what do I know! You lookup the price to get the part number? Why don't you lookup the part number to get the price? A couple of tidbits: That formula does not need to be array entered. it works fine with one exception Then that means it DOESN'T work! <g A better, non-volatile formula to use: =IF(F37="","",INDEX($K$3:$K$1270,MATCH(F37,$AD$3:$ AD$1270,0))) Is there a way to say if the row above is the same value skip to the next value with the same cost? If you have multiple instances of a price how do you know which part number you want returned? Are there any instances where 5 or 10 items may have the same price? How do you know which corresponding part you want? Biff "Joe Gieder" wrote in message ... First, thank you in advance for your help and looking at message. I use this array formula: =IF(F37="","",OFFSET('Priced BOM'!$T$3,MATCH(F37,'Priced BOM'!$AD$3:$AD$1270,0)-1,-9,1,1)) and it works fine with one exception. The formula is copied down many rows and what it does is takes the total cost of a part (F37) and matches it to $AD$3:$AD$1270. When the result is found it then copies the part number, the problem is that if two different part numbers exist with the same cost it will only show the first entry (I know this is how match works, finding the first occurance and then proceeding to the next row). Is there a way to say if the row above is the same value skip to the next value with the same cost? Thanks in advance for the help. Joe |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Link and formula question | Excel Worksheet Functions | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Question | Excel Worksheet Functions | |||
I have a question regarding countif formula. | Excel Worksheet Functions | |||
Formula Question | Excel Discussion (Misc queries) |