Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Lookup/if statement?
I have 2 spreadsheets with data from 2 systems.
What I need is to take specific data from sheet 1 and match it to the corresponding line in sheet 2. The problem comes in that each sheet only has a unique entry if I search based on a combination of 4 columns (item #, date, location and quantity). For any one of these search items there will multiple entries, but for the combination of all 4 there should be one unique entry. How can I accomplish this to compare the two spreadsheets? |
#2
|
|||
|
|||
Excel has a series of Database functions, but to be honest they are hard for
me to use. I have done what you are looking by Concatenating the cells thereby creating the unique data you are looking for. You can create a new column with the concatenated cells +Concatenate (item #,"_", date,"_",location,"_", quantity) You can then create a lookup using vlookup to find the unique value that you are seeking. Another option is a pivot table. Great tool for this, but a bit hard to learn. "Connie" wrote: I have 2 spreadsheets with data from 2 systems. What I need is to take specific data from sheet 1 and match it to the corresponding line in sheet 2. The problem comes in that each sheet only has a unique entry if I search based on a combination of 4 columns (item #, date, location and quantity). For any one of these search items there will multiple entries, but for the combination of all 4 there should be one unique entry. How can I accomplish this to compare the two spreadsheets? |
#3
|
|||
|
|||
CONCATENATION is the key......
Create a new helper column (A)on your Sheet2 that concatenates the four columns together, with a formula like =B2&"_"&C2&"_"&D2&"_"&E2 Then your VLOOKUP formula on sheet1 might look like =VLOOKUP(B2&"_"&C2&"_"&D2&"_"&E2,Sheet2!A:A,1,fals e) Change the cells to fit your data........ Vaya con Dios, Chuck, CABGx3 "Connie" wrote: I have 2 spreadsheets with data from 2 systems. What I need is to take specific data from sheet 1 and match it to the corresponding line in sheet 2. The problem comes in that each sheet only has a unique entry if I search based on a combination of 4 columns (item #, date, location and quantity). For any one of these search items there will multiple entries, but for the combination of all 4 there should be one unique entry. How can I accomplish this to compare the two spreadsheets? |
#4
|
|||
|
|||
Quote:
Are you allowed to modify the spreadsheets to include a new column that would hold the unique key for each? Laura Sallwasser |
#5
|
|||
|
|||
Another option:
=index(othersheet!$e$1:$e$10, match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100) *(c2=othersheet!$c$1:$c$100)*(d2=othersheet!$d$1:$ d$100),0)) Connie wrote: I have 2 spreadsheets with data from 2 systems. What I need is to take specific data from sheet 1 and match it to the corresponding line in sheet 2. The problem comes in that each sheet only has a unique entry if I search based on a combination of 4 columns (item #, date, location and quantity). For any one of these search items there will multiple entries, but for the combination of all 4 there should be one unique entry. How can I accomplish this to compare the two spreadsheets? -- Dave Peterson |
#6
|
|||
|
|||
ps.
I left off this instruction... 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.) Connie wrote: I have 2 spreadsheets with data from 2 systems. What I need is to take specific data from sheet 1 and match it to the corresponding line in sheet 2. The problem comes in that each sheet only has a unique entry if I search based on a combination of 4 columns (item #, date, location and quantity). For any one of these search items there will multiple entries, but for the combination of all 4 there should be one unique entry. How can I accomplish this to compare the two spreadsheets? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Do I need a sumif or sum of a vlookup formula? | Excel Worksheet Functions | |||
IF Statement | New Users to Excel | |||
How to calculate a sum as one outcome of an IF statement | Excel Worksheet Functions | |||
How do I fix a circular reference in a financial statement? | Excel Discussion (Misc queries) | |||
Statement | Excel Worksheet Functions |