• 409K Members
• 6,545 Online
• 466K Conversations

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

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

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

# Re: Index Match Formula Help

Worked like a charm! Thank you

# Re: Index Match Formula Help

You are welcome

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies