Feb 04 2019 06:12 AM
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 |
Feb 04 2019 06:18 AM
Feb 04 2019 06:30 AM
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.