Looking for a double match in an INDEX/MATCH

[HeyInKy]

See attachment. In G4, I need a formula that will do an index/match, but match it to 2 criteria.

The first match criteria finds the correct file number (entered in G1), but when more than 1 file numbers (column B) are identical, I need a system to identify which one of all matching file numbers.

My partial solution was to create a Document field with the formula =COUNTIF($B$2:B2,B2), which will keep count of the number of times that file number appears.

The idea is that, for example, you want the color for the second file number, you would enter 2 in G2.

I've played with OFFSET but couldn't get it to work. My current formula in G4 gets thrown off when a different file number is entered in-between identical file numbers.


Sample Problem 1.xlsx

[sandy666]

maybe try this without formula(s):

[HeyInKy]

Thanks, but my workbook setup demands a formula.

[sandy666]

No problem, good luck

[HeyInKy]

Going to bump this to see if anyone could help today with a formula. Thanks!

[Phuocam]

Try:

=LOOKUP(2,1/(Table1[File '#]=G1)/(Table1[Document '#]=G2),Table1[Color])

[TFS2017]

This article I've literally just read may assist you...rather than me type out a solution - I was actually just looking for a solution to my Excel issue but this might work for you given it mentions multiple criteria.

http://spreadsheeto.com/index-match/

[HeyInKy]

@Phuocam This worked! Thanks!

@TFS2017 - Perfect! And yes! Thank you!