# Index/Match Formula to return multiple results

1. ## Index/Match Formula to return multiple results

Hi, So I've created a formula that automatically returns the PO number to the corresponding offer it is referencing...and vice versa. The issue I am having is that occasionally there are several PO's which are for a single offer, though the formula I've created only returns the first PO number that it finds when I'd prefer it to report every PO number that is connected to the single offer. I understand there is something to do with an array formula and using Control-shift-enter when entering the formula but I can't figure out what to do to report multiple PO numbers.

I'll attach a sample file, The formula I am currently using is:

=IFERROR(INDEX(Pos!\$A\$2:\$A\$22,INDEX(MATCH(A2,Pos!\$H\$2:\$H\$22,0),0)),"")

Mike Stafford

2. ## Re: Index/Match Formula to return multiple results

If you pull in multiple entries, then the rest of your data wont line up (seeing as they are entries, not formulas)?

Also, they work the same, but I changed some of your formulas...
sheet1...
H2=IFERROR(INDEX(Offers!A2:A24,MATCH(B2&C2&D2,INDEX(Offers!B2:B24&Offers!C2:C24&Offers!D2:D24,0),0)),"")

sheet2...
I2=IFERROR(INDEX(Pos!\$A\$2:\$A\$22,MATCH(A2,Pos!\$H\$2:\$H\$22,0)),"")

Not really sure what your expected answer would look like, but this is the kind or ARRAY formula you would use to pull out multiple matches of your data...
=IFERROR(INDEX(Offers!\$A\$2:\$A\$24,SMALL(IF((Offers!\$B\$2:\$B\$24=Pos!B2)*(Offers!\$C\$2:\$C\$24=Pos!C2)*(Offers!\$D\$2:\$D\$24=Pos!D2),ROW(Pos!\$A\$2:\$A\$22)-1),ROWS(\$A\$1:A1))),"")

3. ## Re: Index/Match Formula to return multiple results

I need an array formula that will report multiple PO numbers into each cell on the offers sheet. Since PO #2 and #5 have the same offer corresponding to it, I would like for it to report both of the PO numbers on the offer sheet under offer #032515-01.

4. ## Re: Index/Match Formula to return multiple results

and, like I said, what about the rest of your data? it will then no longer line up proerly

5. ## Re: Index/Match Formula to return multiple results

Yes it would...it wouldn't change anything except mention more than one PO number.The formula is searching for other offer numbers that match the one in the first row, don't see how that'd have any effect on the other data.

6. ## Re: Index/Match Formula to return multiple results

Did you try the 2nd formula I suggested in post 2?

7. ## Re: Index/Match Formula to return multiple results

Yes and its still returning only one PO number per cell. I believe the last function you mentioned with the small command is the correct one I'm just not sure how to apply that formula to what I'm doing. The "small" formula you wrote out is for the filling out the offer numbers but I need a command that will fill out multiple PO numbers for me. I tried changing it to be for what I'm doing but Im having no luck.

8. ## Re: Index/Match Formula to return multiple results

hmm OK, what am I missing here? You perhaps missed entering my formula as an ARRAY (using CTRL SHIFT ENTER, not just enter), and you give negative reps with a comment of "Bunk"?

9. ## Re: Index/Match Formula to return multiple results

Yeah because you really weren't much help, I'm pretty sure you didn't even read what I was asking...What'd you expect?

10. ## Re: Index/Match Formula to return multiple results

Did you enter the formula as an ARRAY formula - using CTRL SHIFT ENTER, and not just ENTER?
You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

11. ## Re: Index/Match Formula to return multiple results

I'm suppose to be using a small formula correct? I made my initial formula an array formula but it's still returning only one value per cell.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1