Vlookup, Index or Match?

I have a spreadsheet that I am trying to get and exact match on a summary page. The criterias are
if the data in B3 & C3 matches the data on a separate tab but same cell references then the data from cells F3:BR3 should be exact for all for all cells that have the exact same criteria as B3 & C3. To explain myself further. If a cell b3 have a # of 00057 and cell C3 have a nuber of 058 then all cells in those two columns that match those criteria should have the same percentage from column F5:BR2631.

This is an index formula I am trying and I am getting #VALUE! ERROR. Would VLOOKUP be better and can someone assist me with the formula.

Also cross posted here
http://www.mrexcel.com/forum/excel-q...dex-match.html

Thanks

cwallace70,

Welcome to the forum!
Attached is an example workbook based on the criteria you described. In Sheet1 cell F3 is this formula:

=INDEX(BASE!$F$4:$BR$124,MATCH(1,INDEX((BASE!$B$4:$B$124=$B3)*(BASE!$C$4:$C$124=$C3),),0),MATCH(F$2,BASE!$F$3:$BR$3,0))

Is something like that what you're looking for?

cwallace70,

In that case, I would need a sample workbook to see what you are trying to achieve, so...

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.
To attach a file, click the "Go Advanced" button and then click the paperclip icon to open the Manage Attachments dialog.

Also, cross posted here:
http://www.mrexcel.com/forum/excel-q...dex-match.html

cwallace70,

Attached is a modified version of your sample file. In the 'summary' sheet cell D3 and copied down is this formula:

=IFERROR(SUM(INDEX(Base!$F$3:$BR$2631,MATCH(1,INDEX((Base!$B$3:$B$2631=B3)*(Base!$C$3:$C$2631=C3),),0),0)),"")

That formula will find the row that matches B and C in the Base sheet, and then sum that row from columns F:BR.

how does the formula know which intersection to use? What number is the number in that intesection being compared against?

Below shows that 0000 & 058 apear 4 times on the base sheet and in row 1 the percentages are different so the results from the formula for 00000 & 058 should be false for what ever row the different oercentage falls in and in row 2 all the %'s are the same so everytime it hits that row it would come back as true because all the percentages match. I hope this explains what I am trying to do. I fit can't be done then it would have to be a manual checking process.

cwallace70,

I have no idea what you mean by

look at E2 (this is where the intersection number is located) and tell me if the percentages in column "E"...

There are no numbers (percentages or otherwise) in column E. Column E are text values. So i instead took your intent as the following:
Find the row in sheet 'Base' where column B and C match the column B and C entries in sheet 'summary'.
Find if all the numbers in sheet 'Base' columns F:BR for that row are identical
If they are, return TRUE
If they are not, return FALSE

That is a rather complex request, so the formula is rather large, but here it is. In sheet 'summary' cell D3 and copied down:

=(COUNTIF(INDEX(Base!$F$3:$BR$2631,MATCH(1,INDEX((Base!$B$3:$B$2631=$B3)*(Base!$C$3:$C$2631=$C3),),0),0),"<>"&INDEX(INDEX(Base!$F$3:$BR$2631,MATCH(1,INDEX((Base!$B$3:$B$2631=$B3)*(Base!$C$3:$C$2631=$C3),),0),0),MATCH(TRUE,INDEX(INDEX(Base!$F$3:$BR$2631,MATCH(1,INDEX((Base!$B$3:$B$2631=$B3)*(Base!$C$3:$C$2631=$C3),),0),0)<>"",),0)))-COUNTBLANK(INDEX(Base!$F$3:$BR$2631,MATCH(1,INDEX((Base!$B$3:$B$2631=$B3)*(Base!$C$3:$C$2631=$C3),),0),0))=0)

As a side note, the formula could probably be shortened if you used a helper column.

That will only look at column F in the matching row. And the row its looking at will be off because the formula is finding a match starting at row 3, not row 1.

Can you please provide a detailed explanation of what you're trying to accomlish as if you were to do it by hand. So the step-by-step logical process that you would go through manually to get the result? Also, the example workbook you keep posting doesn't have the results you want. It just has a couple of formulas that result in errors. Seeing the desired results (typed in manually) would be a big help also.

alright, so...

1) Find all rows in sheet 'Base' where columns B and C match columns B and C in sheet 'summary'
2) Test if all columns F:BR for all of those found rows in sheet 'Base' are identical
3) If they are all identical return TRUE else FALSE

Based on that, there are 4 rows found for 00000 and 064 in sheet 'Base'. Those 4 rows do not have identical information in column F:BR so the result should be FALSE. I say they do not have identical information because:

The found rows are 4, 659, 1314, 1969
Row 4 has: column BA ">11" = 2.00%
Row 659 has: column BA ">11" = 2.00%
Row 1314 has: column AB "2-10" = 5.00%
Row 1969 has: column Z "2-6" = 5.00%

So not only are the populated columns different, but the values are also different.

Is that correct?

???? You are continually giving me more questions. In this data set, the left-hand side (summary sheet) 00000 058 only appears three times. The 4th appearance of 00000 058 on the right-hand side (Base sheet) is lined up with 214XX 086. Also, you have 20% 20% for the first three occurrences and 15% 20% for the 4th occurrence. Where does the 20/20/20/15 come from? In the sample sheets you provided earlier (including the most recent one), the summary sheet had no percentages at all in columns F:BR.

As for whether or not this can be done, I assure whatever you're trying to automate is possible, I just still don't know what it is exactly that you're trying to automate...

cwallace70,

Attached is version 3. In sheet 'summary' cell E3 and copied over and down to BV30 is this formula:

=IF(OR($B3="",$C3="",SUMIFS(INDEX(Base!$F$3:$BW$2622,0,MATCH(E$2,Base!$F$2:$BW$2,0)),Base!$B$3:$B$2622,$B3,Base!$C$3:$C$2622,$C3)=0),"",COUNTIFS(Base!$B$3:$B$2622,$B3,Base!$C$3:$C$2622,$C3)=COUNTIFS(Base!$B$3:$B$2622,$B3,Base!$C$3:$C$2622,$C3,INDEX(Base!$F$3:$BW$2622,0,MATCH(E$2,Base!$F$2:$BW$2,0)),INDEX(Base!$F$3:$BW$2622,MATCH(1,INDEX((Base!$B$3:$B$2622=$B3)*(Base!$C$3:$C$2622=$C3),),0),MATCH(E$2,Base!$F$2:$BW$2,0))))

That formula checks if there are any entries in any of the rows in sheet 'Base' in which column B and C match columns B and C from sheet 'summary'. If there is an entry, it checks if all entries for that column are identical. If they are it returns TRUE else FALSE.

The only times the formula returns TRUE are for 00099 103, 00099 104, and 00099 207 for column "1". Everything else is blank (no entries at all) or FALSE (entries found, but not all were identical). Is that what you're looking for?

You're very welcome

If that takes care of your need, please mark this thread as solved.
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save