Forum Discussion
Index Match Formula Help
I am using an Index Match formula to pull from a data set and create an invoice for my business. I have assigned a unique invoice # to each set of appointment numbers that belong to the same customer. Invoice #1 is customer #1. Invoice #2 is customer 2 and so on. Invoice #1 has 12 unique visit numbers while invoice # 2 has 1 appointment.
On the face of the invoice I can type in "1" and vlookups pull in the information I need based on the Invoice #. What I need to do on the invoice is populate the appointment numbers and stop populating once the invoice # on the face of the invoice no longer matches the invoice # from my data.
My issue currently is that the Appointment numbers are pulling in even if the Invoice # from the data doesn't match the invoice itself.
=INDEX('Combined AR'!$A:$B,MATCH(G8,'Combined AR'!$A:$A,FALSE)+0,0)
=INDEX('Combined AR'!$A:$B,MATCH(G8,'Combined AR'!$A:$A,FALSE)+1,0)
=INDEX('Combined AR'!$A:$B,MATCH(G8,'Combined AR'!$A:$A,FALSE)+2,0) ...etc
| Invoice# | Appointment Number |
| 1 | 6018581 |
| 1 | 6018635 |
| 1 | 6019492 |
| 1 | 6020878 |
| 1 | 6021232 |
| 1 | 6021978 |
| 1 | 6574322 |
| 1 | 6024447 |
| 1 | 6025436 |
| 1 | 6025311 |
| 1 | 6025499 |
| 1 | 6025793 |
| 2 | 6016280 |
| 3 | 154307 |
| 4 | 151946 |
| 4 | 152720 |
| 4 | 155945 |
| 5 | 148620 |
| 5 | 152026 |
| 5 | 152977 |
| 5 | 153084 |
4 Replies
Hi,
For the model like this
you may use formula
=IFERROR(INDEX($C$4:$C$24,AGGREGATE(15,6,1/($B$4:$B$24=E$2)*(ROW($B$4:$B$24)-ROW($B$3)),ROW()-ROW($B$3)+1)),"")
and drag it down till empty rows appear. Please see attached.
Worked like a charm! Thank you
You are welcome
- I should add that G8 is the invoice # from my Invoice tab. A= Invoice # from my data and B= Appointment # from my data.
