index match if?

[adamelston]

Hello I would like to know how to do an index/match with multiple matches.

Eg I would like to return 'date' from another sheet only if 'student ID', 'assessment name' and 'assessment type' also exactly match.

I would be grateful for any help with this.

Thanks
Adam

 

[Special-K99]

Post a sample spreadsheet with expected results, remove any sensitive data, create a mockup example if necessary, remember to include cell references in the header columns /rows.
You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

https://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[adamelston]

Many thanks, I am not sure how to populate Date in Table 1, with the correct date from Table 2. I thought index/match, indexing the date (13/5/19) based on ID = 99 and Color = red, but I am not sure how to do this to capture more than one criteria to match.

Table 1
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]id[/TD]
[TD]Color[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]99[/TD]
[TD]red[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Table 2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]id[/TD]
[TD]Color[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]99[/TD]
[TD]red[/TD]
[TD]13/5/19[/TD]
[/TR]
[TR]
[TD]99[/TD]
[TD]yellow[/TD]
[TD]14/5/19[/TD]
[/TR]
</tbody>[/TABLE]

 

[Special-K99]

This is an array formula, they are slow so this may slow down your spreadsheet depending how many of these you need

in Table1!C1
=INDEX(Table2!C$1:C$2,MATCH(A1,IF(Table2!B$1:B$2=B1,Table2!A$1:A$2),1))
Array formula, use Ctrl-Shift-Enter

 

[adamelston]

Many thanks, I am struggling to make it work but feel I am getting closer...

 

[Special-K99]

Ah, maybe it needs to be this (adjusted row numbers)

in Table1!C2
=INDEX(Table2!C$2:C$3,MATCH(A2,IF(Table2!B$2:B$3=B2,Table2!A$2:A$3),1))
Array formula, use Ctrl-Shift-Enter

 

[adamelston]

I really appreciate your help. (though I think I'm a bit confused), I try this as an array (translating your cell refs):

=INDEX(Table_2[Date],MATCH([ID],IF(Table_2[Colour]=[Colour],Table_2[ID]),1))

It works for table 1 c2, but if i manually populate table 1 b2 with 'yellow' it doesn't return the date I would expect from table 2 (14/5/19), rather it also returns 13/5/19. Am I interpreting your formula incorrectly when I change it to ranges?

 

[Special-K99]

I don't know much about tables but I got it working thus:

All data in Sheet1

Defined a Table for data (Table3) with headers ID Colour and Date from A1:C3 with data starting at row 2
Defined a Table for search criteria (Table4) with headers ID and Colour from E1:F2 with data starting at row 2

in F4
=INDEX(Table3[Date],MATCH(E2,IF(Table3[Colour]=F2,Table3[ID]),1))
Array formula, use Ctrl-Shift-Enter

returns correct values for red and yellow

Your search criteria is [ID] and [Colour]
But your table data is preceded by a table name
Shouldnt this be the same for [ID] and [Colour], ie presumaly Table_1[ID] and Table_1[Colour]

If I put Table4[ID] and Table4[Colour] I get the correct output

Insert the Table name of your search criteria and it should work as per mine

 

[adamelston]

That is sorted, thank you @Special-K99