How to match row and column criteria and return 2nd and 3rd columns?

Lacan

Lacan

Active Member
Joined
Oct 5, 2016
Messages
264
Office Version
  1. 365
Platform
  1. Windows
Hello Guys, 👌
Hope you are fine.

Can you please help, would like to match row and column criteria and return 2nd and 3rd columns.
This is a short sample below, please fill FORMULA in cell D20.
Thank you very much.👌👍👍🍻🍻

2025.xlsx
ABCDEFGHIJKLMNO
1
2LOCALBRISTOL€RANKNICE€RANKPRAGUE€RANK
3301Lx-BRISTOL(Venteira)41639634301Lx-BRISTOL(Venteira)309Lx-NICE(Benfica-Norte)48189492309Lx-NICE(Benfica-Norte)359St-PRAGUE(ChCaparica,Sobreda)3884217 7359St-PRAGUE(ChCaparica,Sobreda)
4302Lx-BRISTOL(Alfragide,ÁLivres)36117559302Lx-BRISTOL(Alfragide,ÁLivres)333Lx-NICE(Carnide)353569810333Lx-NICE(Carnide)353St-PRAGUE(Laranjeiro)2890847 15353St-PRAGUE(Laranjeiro)
5304Lx-BRISTOL(ESol)319909912304Lx-BRISTOL(ESol)331Lx-NICE(SDBenfica-Sul)301106314331Lx-NICE(SDBenfica-Sul)357St-PRAGUE(Caparica,Trafaria)2217141 18357St-PRAGUE(Caparica,Trafaria)
6300Lx-BRISTOL(MÁgua-Norte)153706725300Lx-BRISTOL(MÁgua-Norte)316Lx-NICE(SMMaior)280246616316Lx-NICE(SMMaior)354St-PRAGUE(CPiedade)2114560 19354St-PRAGUE(CPiedade)
7303Lx-BRISTOL(Falagueira,VNova)149303027303Lx-BRISTOL(Falagueira,VNova)311Lx-NICE(COurique-Oeste)272986217311Lx-NICE(COurique-Oeste)356St-PRAGUE(Feijó)2050817 20356St-PRAGUE(Feijó)
8299Lx-BRISTOL(MÁgua-Sul)147821528299Lx-BRISTOL(MÁgua-Sul)332Lx-NICE(SDBenfica-Norte)193298421332Lx-NICE(SDBenfica-Norte)358St-PRAGUE(CCaparica)1696529 22358St-PRAGUE(CCaparica)
9308Lx-NICE(Benfica-Sul)165687423308Lx-NICE(Benfica-Sul)
10310Lx-NICE(Estrela)153743624310Lx-NICE(Estrela)
11314Lx-NICE(SAntónio-Oeste)152952726314Lx-NICE(SAntónio-Oeste)
12313Lx-NICE(Misericórdia)122051629313Lx-NICE(Misericórdia)
13320Lx-NICE(SVicente)108821430320Lx-NICE(SVicente)
14321Lx-NICE(Campolide)90393331321Lx-NICE(Campolide)
15312Lx-NICE(COurique-Este)90326732312Lx-NICE(COurique-Este)
16315Lx-NICE(SAntónio-Este)90267133315Lx-NICE(SAntónio-Este)
17
18
19LOCALPRAGUE€ RANK
20BRICK356St-PRAGUE(Feijó)(fill FORMULA)
21
22
Folha1
Cell Formulas
RangeFormula
F3:F8F3=UNIQUE(C3:C8,0)
J3:J16J3=UNIQUE(G3:G16)
N3:N8N3=UNIQUE(K3:K8)
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
C19ListBRISTOL;NICE;PRAGUE
C20List=INDIRETO(C19)
 
Sort by date Sort by votes
C20=IF(C$19<>"",IF(C$19=$C$19,SUMIF(INDEX($B$3:$M$16,,MATCH($B$19,$B$2:$M$2,0)),$B$20,INDEX($B$3:$M$16,,AGGREGATE(15,6,COLUMN($B$2:$K$2)/($B$2:$K$2=$B$19),COLUMNS($C$19:$C$19)))),SUMIF(INDEX($B$3:$M$16,,MATCH($B$19,$B$2:$M$2,0)),$B$20,INDEX($B$3:$M$16,,AGGREGATE(15,6,COLUMN($B$2:$K$2)/($B$2:$K$2=$B$19),COLUMNS($C$19:$C$19))+1))),"")

Copy across
 
Upvote 0
MARZIOTULLIO said:
C20=IF(C$19<>"",IF(C$19=$C$19,SUMIF(INDEX($B$3:$M$16,,MATCH($B$19,$B$2:$M$2,0)),$B$20,INDEX($B$3:$M$16,,AGGREGATE(15,6,COLUMN($B$2:$K$2)/($B$2:$K$2=$B$19),COLUMNS($C$19:$C$19)))),SUMIF(INDEX($B$3:$M$16,,MATCH($B$19,$B$2:$M$2,0)),$B$20,INDEX($B$3:$M$16,,AGGREGATE(15,6,COLUMN($B$2:$K$2)/($B$2:$K$2=$B$19),COLUMNS($C$19:$C$19))+1))),"")

Copy across
Click to expand...

Dear @MARZIOTULLIO

Thanks for the help.
However this is a very complex and long formula.
Can you please show a much shorter and fresh updated formula?
Thanks again.
 
Upvote 0
C20=IF(C$19<>"",INDEX(OFFSET(INDEX($B$3:$M$16,,MATCH($B$19,$B$2:$M$2,0)),,COLUMNS($C$19:C19)),MATCH($B$20,INDEX($B$3:$M$16,,MATCH($B$19,$B$2:$M$2,0)),0)),"")

Copy across
 
Upvote 0
MARZIOTULLIO said:
C20=IF(C$19<>"",INDEX(OFFSET(INDEX($B$3:$M$16,,MATCH($B$19,$B$2:$M$2,0)),,COLUMNS($C$19:C19)),MATCH($B$20,INDEX($B$3:$M$16,,MATCH($B$19,$B$2:$M$2,0)),0)),"")
Click to expand...

Not working!
Can you please check again?
Thanks.
 
Upvote 0
Book1.xlsx
ABCDEFGHIJKLM
1
2LOCALBRISTOL€RANKNICE€RANKPRAGUE€RANK
3301Lx-BRISTOL(Venteira)41639634301Lx-BRISTOL(Venteira)309Lx-NICE(Benfica-Norte)48189492309Lx-NICE(Benfica-Norte)359St-PRAGUE(ChCaparica,Sobreda)38842177359St-PRAGUE(ChCaparica,Sobreda)
4302Lx-BRISTOL(Alfragide,ÁLivres)36117559302Lx-BRISTOL(Alfragide,ÁLivres)333Lx-NICE(Carnide)353569810333Lx-NICE(Carnide)353St-PRAGUE(Laranjeiro)289084715353St-PRAGUE(Laranjeiro)
5304Lx-BRISTOL(ESol)319909912304Lx-BRISTOL(ESol)331Lx-NICE(SDBenfica-Sul)301106314331Lx-NICE(SDBenfica-Sul)357St-PRAGUE(Caparica,Trafaria)221714118357St-PRAGUE(Caparica,Trafaria)
6300Lx-BRISTOL(MÁgua-Norte)153706725300Lx-BRISTOL(MÁgua-Norte)316Lx-NICE(SMMaior)280246616316Lx-NICE(SMMaior)354St-PRAGUE(CPiedade)211456019354St-PRAGUE(CPiedade)
7303Lx-BRISTOL(Falagueira,VNova)149303027303Lx-BRISTOL(Falagueira,VNova)311Lx-NICE(COurique-Oeste)272986217311Lx-NICE(COurique-Oeste)356St-PRAGUE(Feijó)205081720356St-PRAGUE(Feijó)
8299Lx-BRISTOL(MÁgua-Sul)147821528299Lx-BRISTOL(MÁgua-Sul)332Lx-NICE(SDBenfica-Norte)193298421332Lx-NICE(SDBenfica-Norte)358St-PRAGUE(CCaparica)169652922358St-PRAGUE(CCaparica)
9308Lx-NICE(Benfica-Sul)165687423308Lx-NICE(Benfica-Sul)
10310Lx-NICE(Estrela)153743624310Lx-NICE(Estrela)
11314Lx-NICE(SAntónio-Oeste)152952726314Lx-NICE(SAntónio-Oeste)
12313Lx-NICE(Misericórdia)122051629313Lx-NICE(Misericórdia)
13320Lx-NICE(SVicente)108821430320Lx-NICE(SVicente)
14321Lx-NICE(Campolide)90393331321Lx-NICE(Campolide)
15312Lx-NICE(COurique-Este)90326732312Lx-NICE(COurique-Este)
16315Lx-NICE(SAntónio-Este)90267133315Lx-NICE(SAntónio-Este)
17
18
19LOCALPRAGUE€RANK
20BRICK356St-PRAGUE(Feijó)205081720
Sheet1
Cell Formulas
RangeFormula
E3:E8E3=UNIQUE(B3:B8,0)
I3:I16I3=UNIQUE(F3:F16)
M3:M8M3=UNIQUE(J3:J8)
C20:D20C20=IF(C$19<>"",INDEX(OFFSET(INDEX($B$3:$M$16,,MATCH($B$19,$B$2:$M$2,0)),,COLUMNS($C$19:C19)),MATCH($B$20,INDEX($B$3:$M$16,,MATCH($B$19,$B$2:$M$2,0)),0)),"")
Dynamic array formulas.
 
Last edited:
Upvote 0
Another option
Excel Formula: 
=LET(d,VSTACK(C3:E17,G3:I17,K3:M16),XLOOKUP(C20,TAKE(d,,1),TAKE(d,,-2)))
 
Upvote 0
Solution
Lacan said:
Thank you very much @Fluff !!!
Works Perfect!!! 👍👍🍻
Click to expand...
The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Lacan
  • Solved
For 2 Columns how to Wraprows and then sort it horizontally accordingly with the rank List?
Replies
7
Views
302
Lacan
Lacan
Lacan
  • Solved
Let formula?
2
Replies
12
Views
518
Lacan
Lacan
Lacan
  • Question Question
Very simple and easy question: - How to use Indirect Function + Xlookup with 2 Criteria?
Replies
2
Views
500
Lacan
Lacan

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top