# INDEX MATCH formula help with duplicates

#### gravanoc

##### Board Regular
I've tried making my own formula, then I tried the Internet formulas, but I can't get everything straightened out.
Workbook: Doc v3.xlsx

The relevant information is in yellow or orange colored cells, and most of the workbook has been trimmed down for sensitivity reasons. Given the ISBN for a book, I need to determine how many of each book a particular business has purchased. The ISBNs duplicate frequently.

Currently I have a helper column with the following formula: ='MS - E'!F3+ROWS(\$1:1)*10^-4
It uses the ISBNs as a reference from the second tab.

My primary formula is: =IFERROR(IF(LEFT(INDEX('MS - E'!\$D\$3:\$D\$2923,MATCH(LARGE(\$A\$3:\$A\$2923,ROWS(\$1:1)),\$A\$3:\$A\$2923,0)),10)=LEFT(\$D5,10),SUM(INDIRECT("H" & MATCH(LARGE(\$A\$3:\$A\$2923,ROWS(\$1:1)),\$A\$3:\$A\$2923,0) & ":L" & MATCH(LARGE(\$A\$3:\$A\$2923,ROWS(\$1:1)),\$A\$3:\$A\$2923,0))),""),"")
It uses the business names on the second tabs in the INDEX formula, then uses MATCH on the data in the helper column. The problem is, it seems to be pretty sporadic in which business it chooses with the LARGE formula. If it matches the business name on the first tab, then the SUM & INDIRECT formulas work together to sum up the number of books purchases, but there is a problem there as well. The two MATCHES in the SUM formula match the same row, but I need them to cover a range, if possible. I think that part might work if I change the second MATCH to use a SMALL formula instead, but I still have to get the first half of the formula working to consider that issue.

Thanks. The workbook is linked up top as Doc v3.xlsx.

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

#### gravanoc

##### Board Regular
According to rules, I'm informing that this is now cross-posted here:

Bump

#### gravanoc

##### Board Regular
I ended up using VBA to get the numbers, but even that is running slow. If anyone can help with either a formula or give me an idea of how to optimize the VBA, that would be awesome.

VBA Code:
``````Sub FindISBN()

Dim sdSht As Worksheet
Dim msSht As Worksheet

Dim sdISBN As Range
Dim sdBoard As Range
Dim sdCBoard As Range
Dim msISBN As Range
Dim msSum As Range
Dim msBoard As Range

Dim summation As Long

Set sdSht = Worksheets("Sample Data")
Set msSht = Sheets("Mathology Sales - English")

Set sdISBN = sdSht.Range("Q2:AN2")
Set sdBoard = sdSht.Range("E5:E379")
Set sdCBoard = sdSht.Range("A5:E379")
Set sdWrite = sdSht.Range("Q5:AN379")
Set msISBN = msSht.Range("F5:F2925")
Set msBoard = msSht.Range("D5:D2925")

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For k = 0 To 375

For i = 0 To 23

For j = 0 To 2920

If sdISBN.Cells(1, 1 + i).Value = msISBN.Cells(1 + j, 1).Value Then

If Left(sdBoard.Cells(k + 1, 1).Value, 5) = Left(msBoard.Cells(j + 1, 1).Value, 5) Then

summation = WorksheetFunction.Sum(msSht.Range(msISBN.Cells(1 + j, 3), msISBN.Cells(1 + j, 7)))
sdWrite.Cells(k + 1, i + 1).Value = sdWrite.Cells(k + 1, i + 1).Value + summation

End If

End If

Next

Next

Next

End Sub``````

Bump

Replies
6
Views
164
Replies
4
Views
171
Replies
9
Views
327
Replies
2
Views
449
Replies
5
Views
122