# multiple match/index formula

1. ## multiple match/index formula

i need to retrieve a number from a spreadsheet but only if two values are found to match. i've attached an example that shows my dilemma. i'm trying to think of a way but i can't figure it out. what i've come up with in my head is something like a If(And(Index(Match)), Index(Match)) function.

in the example file: if D matches a value in A AND E matches B, then retrieve the value in F.

any help would be appreciated!  Register To Reply

2. ## Re: multiple match/index formula

anguyen27,

Welcome to the forum!
If you meant to attach a file, it looks like it got missed because there is no attachment. As for your question, something like this should work for you: ``Please Login or Register  to view this content.``  Register To Reply

3. ## Re: multiple match/index formula

oops, here's the file. i tried what you suggested and it didn't work.  Register To Reply

4. ## Re: multiple match/index formula

anguyen27,

What are the expected results and where do they go? (Column C? Column G? Elsewhere?) The only ones that match are 4d (CC) and 8h (FF). Because of that, all the others would result in an error. What you like instead of an error?  Register To Reply

5. ## Re: multiple match/index formula

lets say for this example, they'd just go in H. you're right, only those two would match. it doesn't matter if the matches are on the same row, but i just want F to be returned if D matches a value in A and E matches the adjacent value in B.  Register To Reply

6. ## Re: multiple match/index formula

this is what i had previously but i misunderstood the requirements. before i thought i would have to match one value in one column with a value in another sheet. if there wasn't, it'd just check the next column (up to 4 columns).

=IF(ISNA(INDEX(Sheet1!\$E\$2:\$E\$17349, MATCH(AJ2, Sheet1!\$D\$2:\$D\$17349, 0))), IF(ISNA(INDEX(Sheet1!\$E\$2:\$E\$17349, MATCH(BN2, Sheet1!\$D\$2:\$D\$17349, 0))), IF(ISNA(INDEX(Sheet1!\$E\$2:\$E\$17349, MATCH(AI2, Sheet1!\$C\$2:\$C\$17349, 0))),IF(ISNA(INDEX(Sheet1!\$E\$2:\$E\$17349, MATCH(AH2, Sheet1!\$B\$2:\$B\$17349, 0))),"",INDEX(Sheet1!\$E\$2:\$E\$17349, MATCH(AH2, Sheet1!\$B\$2:\$B\$17349, 0))), INDEX(Sheet1!\$E\$2:\$E\$17349, MATCH(AI2, Sheet1!\$C\$2:\$C\$17349, 0))), INDEX(Sheet1!\$E\$2:\$E\$17349, MATCH(BN2, Sheet1!\$D\$2:\$D\$17349, 0))), INDEX(Sheet1!\$E\$2:\$E\$17349, MATCH(AJ2, Sheet1!\$D\$2:\$D\$17349, 0)))

but now we want two matches so if D matches a value in A and E matches the adjacent B, then give us F  Register To Reply

7. ## Re: multiple match/index formula

anguyen27,

Attached is a modified version of your example workbook. In cell H1 and copied down is this formula: ``Please Login or Register  to view this content.``
Note this is basically identical to the formula I provided earlier.  Register To Reply

8. ## Re: multiple match/index formula

hi,
you can use another formula below: ``Please Login or Register  to view this content.``
see attached file...  Register To Reply

9. ## Re: multiple match/index formula

@ tigeravatar

thanks, for some reason i didn't think it worked earlier. now i modified it to be:

=IF(ISNA(INDEX(\$F\$1:\$F\$6,MATCH(1,INDEX((\$A\$1:\$A\$6=D1)*(\$B\$1:\$B\$6=E1),),0))), "", INDEX(\$F\$1:\$F\$6,MATCH(1,INDEX((\$A\$1:\$A\$6=D1)*(\$B\$1:\$B\$6=E1),),0)))

now i have to try X match AND (Y match or Z match). that's going to be pretty long.  Register To Reply

10. ## Re: multiple match/index formula

i'm assuming the * is AND, how do i do OR?  Register To Reply

11. ## Re: multiple match/index formula

anguyen,

Can you provide a sample workbook showing the new data and expected results?  Register To Reply

12. ## Re: multiple match/index formula

here you go  Register To Reply

13. ## Re: multiple match/index formula

I don't get it, don't they all match?

In row 1, you have 1a9 and 1b9. 1=1, and 9=9, so its a match
In row 2, you have 3c6 and 3c5. 3=3 and c=c so its also a match.

So in the provided workbook, they are all matches. Is that true? In that case, you can use this array formula. Note that array formulas must be entered with Ctrl+Shift+Enter and not just Enter. That's how the formula gets surrounded by the curly braces {}. Don't try to add those yourself. ``Please Login or Register  to view this content.``  Register To Reply

14. ## Re: multiple match/index formula

I just changed it up, I guess I didn't change enough. Here's the new edit.  Register To Reply

15. ## Re: multiple match/index formula

In cell L1 and copied down: ``Please Login or Register  to view this content.``  Register To Reply

16. ## Re: multiple match/index formula

that almost helped. why is the lookup value in the Match function = 1? Match(1,  Register To Reply

17. ## Re: multiple match/index formula

Because this part creates an array of 1 (both were true) and 0 (at least one of the conditions was not met) values: ``Please Login or Register  to view this content.``
The Match is looking for the first instance where both were true, so it is matching a 1 against an array of 1's and 0's. (There will almost always only be a single 1).  Register To Reply

18. ## Re: multiple match/index formula

so i'm trying to implement this in my working file. it was a little more complicated than i thought. i think it's right but will update after i check with the file owner.

=IFERROR(INDEX(Sheet1!\$E\$2:\$E\$17349,MATCH(1,INDEX((Sheet1!\$B\$2:\$B\$17349=\$AH2)*(Sheet1!\$D\$2:\$D\$17349=\$BN2),),0)),IF(OR(ISBLANK(AI2),ISBLANK(AJ2)),"",IFERROR(INDEX(Sheet1!\$E\$2:\$E\$17349,MATCH(1,INDEX((Sheet1!\$C\$2:\$C\$17349=\$AI2)*(Sheet1!\$D\$2:\$D\$17349=\$AJ2),),0)),IFERROR(INDEX(Sheet1!\$E\$2:\$E\$17349,MATCH(1,INDEX((Sheet1!\$C\$2:\$C\$17349=\$AI2)*(Sheet1!\$D\$2:\$D\$17349=\$BN2),),0)),""))))  Register To Reply

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