Index Match Formula Help

Copper Contributor

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
16018581
16018635
16019492
16020878
16021232
16021978
16574322
16024447
16025436
16025311
16025499
16025793
26016280
3154307
4151946
4152720
4155945
5148620
5152026
5152977
5153084
4 Replies
I should add that G8 is the invoice # from my Invoice tab. A= Invoice # from my data and B= Appointment # from my data.

Hi,

 

For the model like this

image.png

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