Lookup/if statement?

[Guest]

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?

 

[Guest]

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.

 

[Guest]

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,false)

Change the cells to fit your data........

Vaya con Dios,
Chuck, CABGx3

 

[Dave Peterson]

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))

 

[Dave Peterson]

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.)