Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match data in 2 columns and return data from 3rd column
I know I have done this in the past but can't remember how I did it.
I have my main spreadsheet, customer.xls with zip and weights. Column A Column B Zip Wt I have a second spreadsheet, prices.xls that has zip, weights and dollar amounts. I can't copy and paste because this spreadsheet is only for shipments weighing less than 200 lbs. Column A Column B Column C Zip Wt $ I need the following, If the zip and weight on the customer.xls is the same as the zip and wt on the price spreadsheet then return the $ on the customer.xls. Can anyone help? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match data in 2 columns and return data from 3rd column
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 only use the whole column in xl2007. 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)) gwtreece wrote: I know I have done this in the past but can't remember how I did it. I have my main spreadsheet, customer.xls with zip and weights. Column A Column B Zip Wt I have a second spreadsheet, prices.xls that has zip, weights and dollar amounts. I can't copy and paste because this spreadsheet is only for shipments weighing less than 200 lbs. Column A Column B Column C Zip Wt $ I need the following, If the zip and weight on the customer.xls is the same as the zip and wt on the price spreadsheet then return the $ on the customer.xls. Can anyone help? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
counting data in one column and match with data in another column | Excel Discussion (Misc queries) | |||
LOOKUP two data sets for match - return 1 or 0 - Please help! | Excel Worksheet Functions | |||
match columns and associated data | Excel Discussion (Misc queries) | |||
MATCH UP DATA IN COLUMNS | Excel Worksheet Functions | |||
Match Each Numeric occurrence and Return Individual Rows of Data | Excel Worksheet Functions |