Highlighted
New Contributor

# 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
Highlighted

# Re: Index Match Formula Help

I should add that G8 is the invoice # from my Invoice tab. A= Invoice # from my data and B= Appointment # from my data.
Highlighted

# Re: Index Match Formula Help

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.

Highlighted

# Re: Index Match Formula Help

Worked like a charm! Thank you

Highlighted

You are welcome