MATCH with multiple criteria across multiple columns

[MS93CP]

Hi Y'all!

I'm trying to find the row number where:
FORMEDIT A:A = B2
and
FORMEDIT K:Z = A2

The contents of A2 could be any of columns K to Z.

[COLOR=black][FONT=Inconsolata]=[/FONT][/COLOR][COLOR=black][FONT=Inconsolata]match[/FONT][/COLOR][COLOR=black][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]1[/FONT][/COLOR][COLOR=black][FONT=Inconsolata],[/FONT][/COLOR][COLOR=black][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#7E3794][FONT=Inconsolata]FORMEDIT!K:Z[/FONT][/COLOR][COLOR=black][FONT=Inconsolata] [/FONT][/COLOR][COLOR=black][FONT=Inconsolata]=[/FONT][/COLOR][COLOR=black][FONT=Inconsolata] A2[/FONT][/COLOR][COLOR=black][FONT=Inconsolata])[/FONT][/COLOR][COLOR=black][FONT=Inconsolata]*[/FONT][/COLOR][COLOR=black][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#A61D4C][FONT=Inconsolata]FORMEDIT!A:A[/FONT][/COLOR][COLOR=black][FONT=Inconsolata] [/FONT][/COLOR][COLOR=black][FONT=Inconsolata]=[/FONT][/COLOR][COLOR=black][FONT=Inconsolata] [/FONT][/COLOR][COLOR=#4285F4][FONT=Inconsolata]B2[/FONT][/COLOR][COLOR=black][FONT=Inconsolata])[/FONT][/COLOR][COLOR=black][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]0[/FONT][/COLOR][COLOR=black][FONT=Inconsolata])[/FONT][/COLOR]

Then using that row number, I'm then putting it together into an index|match.

[COLOR=black][FONT=Inconsolata]=[/FONT][/COLOR][COLOR=black][FONT=Inconsolata]index[/FONT][/COLOR][COLOR=black][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#F7981D][FONT=Inconsolata]FORMEDIT!F:H[/FONT][/COLOR][COLOR=black][FONT=Inconsolata],[/FONT][/COLOR][COLOR=black][FONT=Inconsolata]match[/FONT][/COLOR][COLOR=black][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]1[/FONT][/COLOR][COLOR=black][FONT=Inconsolata],[/FONT][/COLOR][COLOR=black][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#7E3794][FONT=Inconsolata]FORMEDIT!K:Z[/FONT][/COLOR][COLOR=black][FONT=Inconsolata] [/FONT][/COLOR][COLOR=black][FONT=Inconsolata]=[/FONT][/COLOR][COLOR=black][FONT=Inconsolata] A2[/FONT][/COLOR][COLOR=black][FONT=Inconsolata])[/FONT][/COLOR][COLOR=black][FONT=Inconsolata]*[/FONT][/COLOR][COLOR=black][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#A61D4C][FONT=Inconsolata]FORMEDIT!A:A[/FONT][/COLOR][COLOR=black][FONT=Inconsolata] [/FONT][/COLOR][COLOR=black][FONT=Inconsolata]=[/FONT][/COLOR][COLOR=black][FONT=Inconsolata] [/FONT][/COLOR][COLOR=#4285F4][FONT=Inconsolata]B2[/FONT][/COLOR][COLOR=black][FONT=Inconsolata])[/FONT][/COLOR][COLOR=black][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]0[/FONT][/COLOR][COLOR=black][FONT=Inconsolata])[/FONT][/COLOR][COLOR=black][FONT=Inconsolata])[/FONT][/COLOR]

I'm aware that MATCH can't use multiple columns however, and so am searching for some help how to achieve the above! Let me know if you've any questions

Thank you!

 

[Muhammad_Usman]

[FONT=&quot]Try array formula, entered with control + shift + enter.[/FONT]

[/FONT][/COLOR][COLOR=black][FONT=Inconsolata]{=[/FONT][/COLOR][COLOR=black][FONT=Inconsolata]index[/FONT][/COLOR][COLOR=black][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#F7981D][FONT=Inconsolata]FORMEDIT!F:H[/FONT][/COLOR][COLOR=black][FONT=Inconsolata],[/FONT][/COLOR][COLOR=black][FONT=Inconsolata]match[/FONT][/COLOR][COLOR=black][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]1[/FONT][/COLOR][COLOR=black][FONT=Inconsolata],[/FONT][/COLOR][COLOR=black][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#7E3794][FONT=Inconsolata]FORMEDIT!K:Z[/FONT][/COLOR][COLOR=black][FONT=Inconsolata]=[/FONT][/COLOR][COLOR=black][FONT=Inconsolata] A2[/FONT][/COLOR][COLOR=black][FONT=Inconsolata])[/FONT][/COLOR][COLOR=black][FONT=Inconsolata]*[/FONT][/COLOR][COLOR=black][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#A61D4C][FONT=Inconsolata]FORMEDIT!A:A[/FONT][/COLOR][COLOR=black][FONT=Inconsolata]=[/FONT][/COLOR][COLOR=#4285F4][FONT=Inconsolata]B2[/FONT][/COLOR][COLOR=black][FONT=Inconsolata])[/FONT][/COLOR][COLOR=black][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]0[/FONT][/COLOR][COLOR=black][FONT=Inconsolata])[/FONT][/COLOR][COLOR=black][FONT=Inconsolata])}[/FONT][/COLOR][COLOR=black][FONT=Inconsolata]

 

[MS93CP]

Unfortunately I get the error "MATCH range must be a single row or a single column."

 

[Muhammad_Usman]

Unfortunately I get the error "MATCH range must be a single row or a single column."

IS THIS WORK FOR YOU??
ADD COLUMN ACCORDING YOU NEEDS LIKE "&FORMEDIT!M:M&FORMEDIT!N:N"

[COLOR=#333333][FONT=Inconsolata]{=[/FONT][/COLOR][COLOR=#333333][FONT=Inconsolata]index[/FONT][/COLOR][COLOR=#333333][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#333333][FONT=Inconsolata]FORMEDIT!F:H[/FONT][/COLOR][COLOR=#333333][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#333333][FONT=Inconsolata]match[/FONT][/COLOR][FONT=Inconsolata][COLOR=#333333](A[/COLOR][COLOR=#1155cc]2&B2[/COLOR][/FONT][COLOR=#333333][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#333333][FONT=Inconsolata]FORMEDIT!K:K[/FONT][/COLOR][COLOR=#333333][FONT=Verdana][FONT=Inconsolata]&[/FONT][/FONT][/COLOR][COLOR=#333333][FONT=Verdana][FONT=Inconsolata]FORMEDIT!L:L&[/FONT][/FONT][/COLOR][COLOR=#333333][FONT=Verdana][FONT=Inconsolata]FORMEDIT!A:A[/FONT][/FONT][/COLOR][COLOR=#333333][FONT=Verdana][FONT=Inconsolata],[/FONT][/FONT][/COLOR][COLOR=#333333][FONT=Verdana][FONT=Inconsolata]0[/FONT][/FONT][/COLOR][COLOR=#333333][FONT=Verdana][FONT=Inconsolata])[/FONT][/FONT][/COLOR][COLOR=#333333][FONT=Verdana][FONT=Inconsolata])}[/FONT][/FONT][/COLOR]

 

[MS93CP]

IS THIS WORK FOR YOU??
ADD COLUMN ACCORDING YOU NEEDS LIKE "&FORMEDIT!M:M&FORMEDIT!N:N"

[COLOR=#333333][FONT=Inconsolata]{=[/FONT][/COLOR][COLOR=#333333][FONT=Inconsolata]index[/FONT][/COLOR][COLOR=#333333][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#333333][FONT=Inconsolata]FORMEDIT!F:H[/FONT][/COLOR][COLOR=#333333][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#333333][FONT=Inconsolata]match[/FONT][/COLOR][FONT=Inconsolata][COLOR=#333333](A[/COLOR][COLOR=#1155cc]2&B2[/COLOR][/FONT][COLOR=#333333][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#333333][FONT=Inconsolata]FORMEDIT!K:K[/FONT][/COLOR][COLOR=#333333][FONT=Verdana][FONT=Inconsolata]&[/FONT][/FONT][/COLOR][COLOR=#333333][FONT=Verdana][FONT=Inconsolata]FORMEDIT!L:L&[/FONT][/FONT][/COLOR][COLOR=#333333][FONT=Verdana][FONT=Inconsolata]FORMEDIT!A:A[/FONT][/FONT][/COLOR][COLOR=#333333][FONT=Verdana][FONT=Inconsolata],[/FONT][/FONT][/COLOR][COLOR=#333333][FONT=Verdana][FONT=Inconsolata]0[/FONT][/FONT][/COLOR][COLOR=#333333][FONT=Verdana][FONT=Inconsolata])[/FONT][/FONT][/COLOR][COLOR=#333333][FONT=Verdana][FONT=Inconsolata])}[/FONT][/FONT][/COLOR]

Unfortunately, it then says that the value can't be found

Probably a lot easier with some sample data:

TIME	COLLECTION POINT	DELIVERY POINT	BOX ID	BOX ID	BOX ID
10:00	A	B	1	2
11:00	C	A	4	6	8
12:00	A	C	4	8	2
12:00	D	A	3

<tbody>
</tbody>

And then there is a list of all box IDs:

BOX ID	TIME LAST SCANNED	COLLECTION POINT	DELIVERY POINT
1
2
3
4	12:00	FORMULA HERE
5
6
7
8
9

<tbody>
</tbody>

So the FORMULA is to look through A:A of Table 1 to find 12:00, and look through D:F of Table 1 to find "4".
So the output would be A in collection point and C in delivery point.

Any suggestions appreciated!

 

[Muhammad_Usman]

[/CODE]

TIME	COLLECTION POINT	DELIVERY POINT	BOX ID	BOX ID	BOX ID
10:00	A	B	1	2
11:00	C	A	4	6	8
12:00	A	C	4	8	2
12:00	D	A	3
BOX ID	TIME LAST SCANNED	COLLECTION POINT	DELIVERY POINT
1
2
3
4	12:00	A	C
5
6
7
8
9 For Calculation A
=INDEX($B$2:$B$5,MATCH(B12,A2:A5,0)) For Calculation C =INDEX($C$2:$C$5,MATCH(B12,A2:A5,0))[/TD] [TD][/TD] [TD][/TD] [TD][/TD] [TD][/TD] [TD] [/TD] [/TR] </tbody>[/TABLE]

<colgroup><col><col><col><col><col span="2"></colgroup><tbody>

 

[Aladin Akyurek]

Row\Col	A​	B​	C​	D​	E​	F​
1​	TIME	COLLECTION POINT	DELIVERY POINT	BOX ID	BOX ID	BOX ID
2​	10:00	A	B	1	2
3​	11:00	C	A	4	6	8
4​	12:00	A	C	4	8	2
5​	12:00	D	A	3
6​
7​
8​	BOX ID	TIME LAST SCANNED	COLLECTION POINT	DELIVERY POINT
9​	1
10​	2
11​	3
12​	4	12:00	A	C
13​	5
14​	6
15​	7
16​	8
17​	9

In C12 control+shift+enter, not just enter, and copy across to D12:

=IF(SUM($A$2:$A$5=$B12,IF($D$2:$F$5=$A12,1)),INDEX(B$2:B$5,MIN(IF($A$2:$A$5=$B12,IF($D$2:$F$5=$A12,ROW(B$2:B$5)-ROW(B$2)+1)))),"not found")

 

[MS93CP]

That works perfectly!
Are you able to break it down so I better understand what the formula is doing?
Thank you!

 

[Aladin Akyurek]

That works perfectly!
Are you able to break it down so I better understand what the formula is doing?
Thank you!

The IF bit within MIN, i.e.

MIN(IF(...))

returns relative (a) row numbers of records for which 12:00 (time) and 4 (box id) holds.

MIN picks out (b) the smallest row number from the (a) result if that exist; it returns otherwise a 0.

INDEX returns from the range it is fed with a result from the position the (b) result indicates.

Note that a (b) result of 0 would force INDEX to return a wrong item while the conditions do not hold at all. So IF(SUM(...) tries to count how many records there are for which the conditions hold. If the this count is 0, we skip the INDEX bit and get "not found". Otherwise the INDEX bit proceeds with the desired result.