Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look up function for multiple criteria
I have a spreadsheet that has MFG #, Manufacturer, Description and Unit
Price. I need a formula that will look up and match at least the data in 3 columns (MFG #, Manufacturer and Unit Price) in another workbook and bring back the quantity shipped for the row that matches that criteria. Can you help with a formula? Maybe a combination of formula (if that's possible) such as look up MFG #.... and if the manufacturer and unit price match, give me the quantity shipped.... PLEASE help and let me know ASAP if you know the answer :o).... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look up function for multiple criteria
Saved from a previous post:
If you want exact matches for just two columns (and return a value from a third), you could use: =index(othersheet!$c$1:$c$100, match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0)) (all in one cell) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can't use the whole column. This returns the value in othersheet column C when column A and B (of othersheet) match A2 and B2 of the sheet with the formula. And you can add more conditions by just adding more stuff to that product portion of the formula: =index(othersheet!$d$1:$d$100, match(1,(a2=othersheet!$a$1:$a$100) *(b2=othersheet!$b$1:$b$100) *(c2=othersheet!$c$1:$c$100),0)) Dee wrote: I have a spreadsheet that has MFG #, Manufacturer, Description and Unit Price. I need a formula that will look up and match at least the data in 3 columns (MFG #, Manufacturer and Unit Price) in another workbook and bring back the quantity shipped for the row that matches that criteria. Can you help with a formula? Maybe a combination of formula (if that's possible) such as look up MFG #.... and if the manufacturer and unit price match, give me the quantity shipped.... PLEASE help and let me know ASAP if you know the answer :o).... -- Dave Peterson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look up function for multiple criteria
Thank you!!! I actually had just found that formula, but was forgetting to
hit ctrl shift enter instead of just enter and it wasn't working. After reading your post... it worked perfectly!!! THANK YOU THANK YOU THANK YOU!!! "Dave Peterson" wrote: Saved from a previous post: If you want exact matches for just two columns (and return a value from a third), you could use: =index(othersheet!$c$1:$c$100, match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0)) (all in one cell) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can't use the whole column. This returns the value in othersheet column C when column A and B (of othersheet) match A2 and B2 of the sheet with the formula. And you can add more conditions by just adding more stuff to that product portion of the formula: =index(othersheet!$d$1:$d$100, match(1,(a2=othersheet!$a$1:$a$100) *(b2=othersheet!$b$1:$b$100) *(c2=othersheet!$c$1:$c$100),0)) Dee wrote: I have a spreadsheet that has MFG #, Manufacturer, Description and Unit Price. I need a formula that will look up and match at least the data in 3 columns (MFG #, Manufacturer and Unit Price) in another workbook and bring back the quantity shipped for the row that matches that criteria. Can you help with a formula? Maybe a combination of formula (if that's possible) such as look up MFG #.... and if the manufacturer and unit price match, give me the quantity shipped.... PLEASE help and let me know ASAP if you know the answer :o).... -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Criteria for datbase function in one row? | Excel Worksheet Functions | |||
criteria function using max | Excel Worksheet Functions | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
include criteria to 'rank based array function' | Excel Discussion (Misc queries) | |||
Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions |