How to compare a combination of 3 fields in 2 sheets

**Vescalona** · 04-24-2013, 02:37 PM

I have two sheets. In each sheet there are 4 columns. The unique combination of the first 3 columns has a value which is presented in column #4. For example the first 3 columns are account, cost center , product and the fourth column is $100. the second sheet has the same format. Is there a way to present in a single sheet all the columns of the 2 sheets in a row? For example account, cost center, product, $100 (these 4 from sheet 1) account, cost center, product $175 (these last four from sheet 2).All in one row to compare the fileds and amount.

**Lemice** · 04-24-2013, 02:39 PM

Hello,

The short answer is, yes you can, by using VLOOKUP if all datas are unique or you combine them together to make them unique, or by using INDEX with combination of SMALL to deal with duplicated datas.
If you can provide a sample file, it will be easier for others to help you too.

**Vescalona** · 04-24-2013, 03:09 PM

Thanks Lem.

The values in a field are not unique, but the combination of the 3 should be. What do you mean by combine them together? what function should I use?

**Lemice** · 04-24-2013, 03:17 PM

What I mean is like this,

Assuming you are looking for account name "Vesca", cost center "CA", product "Excel", and the forth column is "$100".

Now if you look them up individually, there might be 5 Vesca, 4 CA, 10 Excel and a hundreds (maybe) of $100. But the number of "Vesca at CA with product Excel and ... has $100", there's only a handful (1, in worst case, more than 1 ...).

So instead of MATCH(A5, C5:C15) for example (match value in A5 in C5 to C15), you can make it MATCH(A5&B5,C5:C15&D5:D15) (match value combined by A5 and B5, in a "new" column by combining C5 to C15 with D5 and D15).

For the function you should use, here are the lists of them that might be helpful - VLOOKUP, INDEX, MATCH, (In case there are duplicated, you might have to use SMALL, ROW)

If you can provide a sample file, it will be much easier to illustrate it. It doesn't have to be exact data you have in your real file, can be anything but similar in layout and relevant in structure, and don't have to be large, only a handful of rows and columns will do.

**Vescalona** · 04-24-2013, 03:31 PM

Please see included sample file.

**Lemice** · 04-24-2013, 03:58 PM

This is the Array formula that I used

Please Login or Register  to view this content.

Because it is an Array formula, you will have to hold Ctrl-Shift and hit Enter to enter the formula (If it's wrapped inside a { }, then you did it right).
Here is the sample file with formula.

Note that the product column is text right? So I went ahead and convert the column in worksheet 2 to text.

Same thing with the Cost Center column in both worksheets.

But in any case you wanted them to be number, or if in your file one of them is number, then let me know because currently this formula works under the assumption that in both worksheet, Product and Cost Center column will contain text, not number).