# Index, Match, and/or SumProduct solution?

1. ## Index, Match, and/or SumProduct solution?

Hi. There are two sheets with similar data, but the data may be on different rows. The data in columns A,B,C,D are always the same and accurate, albeit possibly on different rows on the two sheets. The data in column E may be different or blank, though. But it shouldn't be.
What might be the best way to compare the concatenation of A&B&C&D with the values in column E? I'm thinking it would be an Index or Match or SumProduct formula, or a combination of them, but I cannot think of how best to go about this. Any help or advice would be greatly appreciated. Thanks!

Frank

2. ## Re: Index, Match, and/or SumProduct solution?

hi Frank, it's better you upload a sample Excel file so we can help you better. but if you can relate, here goes:
=INDEX(Sheet2!\$E\$2:\$E\$10,MATCH(A2&B2&C2&D2,Sheet2!\$A\$2:\$A\$10&Sheet2!\$B\$2:\$B\$10&Sheet2!\$C\$2:\$C\$10&Sheet2!\$D\$2:\$D\$10,0))

this is assuming your 1st sheet starts the data in row 2, Sheet 2 from A2:E10. you must paste this inside the formula bar & press CTRL + SHIFT + ENTER. if your data is huge, this array formula will slow down Excel. you might then want to use a helper column say in column F for sheet2.
=A2&B2&C2&D2

then your formula in sheet1 as:
=INDEX(Sheet2!\$E\$2:\$E\$10,MATCH(A2&B2&C2&D2,Sheet2!\$F\$2:\$F\$9,0))

Edit: or maybe something even more complicated, but non-array (dont have to press CTRL + SHIFT + ENTER):
=INDEX(Sheet2!\$E\$2:\$E\$10,MATCH(1,INDEX((Sheet2!\$A\$2:\$A\$10=A2)*(Sheet2!\$B\$2:\$B\$10=B2)*((Sheet2!C\$2:\$C\$10=C2)*(Sheet2!\$D\$2:\$D\$10=D2)),),0))

3. ## Re: Index, Match, and/or SumProduct solution?

simpler non array version of
=INDEX(Sheet2!\$E\$2:\$E\$10,MATCH(A2&B2&C2&D2,Sheet2!\$A\$2:\$A\$10&Sheet2!\$B\$2:\$B\$10&Sheet2!\$C\$2:\$C\$10&Sheet2!\$D\$2:\$D\$10,0))

is
=INDEX(Sheet2!\$E\$2:\$E\$10,MATCH(A2&B2&C2&D2,INDEX(Sheet2!\$A\$2:\$A\$10&Sheet2!\$B\$2:\$B\$10&Sheet2!\$C\$2:\$C\$10&Sheet2!\$D\$2:\$D\$10,0),0))

4. ## Re: Index, Match, and/or SumProduct solution?

Excellent, yes, I can sort of visualize how that would work. I have uploaded an example workbook. In what column would I enter the formulas you suggested? Also, would it be possible to use a VBA script to do this? Thanks again!

Frank

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1