Index match - finding where two values 1st appear together

[chrisellis250]

Hello all,

I have a small table on Sheet9. In column B & C are a series of numbers.

The match number for column B will appear in columns E or G in Sheet 2 and the matching number for column C will appear in Column I on Sheet 2. The range is dynamic in sheet 2, based on data that is imported.

I want to return the value from Column B in Sheet2 in H3 on Sheet9 for the 1st instance that the values in Columns B and C from Sheet 9 appear in Columns E/G and I in Sheet 2...


Anyone assist?

[63falcondude]

I recommend uploading a small representative sample of your data along with the desired results (which you can enter manually) based on that data.

To upload an Excel workbook, follow these steps:
1) Click on "Go Advanced"
2) Click on "Manage Attachments"
3) Click on "Choose File"
4) Choose your file and click on "Open"
5) Click on "Upload"
6) Click on "Close this window"

[chrisellis250]

Please see the attached.

Hopefully the colour coding helps

[63falcondude]

Try this in H3:

=INDEX(Sheet2!B$2:B$13,IFERROR(MATCH($B3&$C3,Sheet2!$E$2:$E$13&Sheet2!$I$2:$I$13,0),MATCH($B3&$C3,Sheet2!$F$2:$F$13&Sheet2!$I$2:$I$13,0))) Ctrl Shift Enter

Drag to the right and down.

[chrisellis250]

I'm getting N/A on it

[63falcondude]

You have to enter the formula with Ctrl Shift Enter instead of just Enter.

See the bold part of post #4.

[chrisellis250]

I have entered it like that but I still get an error

[63falcondude]

That's odd. I am not getting an error on my end.

See attachment.

[johnmpl]

Hi, to both!

You could try this too:
[H3] : =INDEX(Sheet2!B$2:B$13,MATCH(1,MMULT((Sheet2!$E$2:$F$13=$B3)*(Sheet2!$I$2:$I$13=$C3),{1;1}),))

And drag it right and down. Blessings!

[63falcondude]

Nice. That gives me an idea.

Here is another option that does not require Ctrl Shift Enter:

H3 =INDEX(Sheet2!B:B,AGGREGATE(15,6,ROW(Sheet2!$E$2:$F$13)/(($B3=Sheet2!$E$2:$F$13)*($C3=Sheet2!$I$2:$I$13)),1))

[chrisellis250]

Fantastic. Johnmpl's formula worked well. Is there a way I could do this to look at the last instance the two numbers appear together?

[63falcondude]

Try this in H3:

=LOOKUP(2,1/((Sheet2!$E$2:$E$13=$B3)*(Sheet2!$I$2:$I$13=$C3)),Sheet2!B$2:B$13)

Drag to the right and down.