Lookup The First, Second, Third, Or Nth Match Value In Excel

KRIXX · Dec 14, 2018

I Have Big Database Of Accounts In Excel File And I Want Organize The Database In Following Manner.

For Example Purpose I Am Giving You Small Tables To Understand My Concern.

In “Sheet1” I Have Account Numbers Of The Account

Account No	Payment
5031051019230
5031008033030
5031008002739
5031008031461

And

In “Sheet2” I Have Account Numbers Of The Account

Account No	Payment
5031051019230	8551
5031008033030	7852
5031008002739	6585
5031008031461	9548
5031008002739	7432
5031051019230	3688
5031008033030	3552
5031008031461	8896
5031008031461	9565
5031051019230	6854
5031008033030	9685

Now

I Want To Lookup In“Sheet1” And Result Should Be Like Below Table.

Account No	Payment	Payment	Payment
5031051019230	8551	3688	6854
5031008033030	7852	3552	9685
5031008002739	6585	7432
5031008031461	9548	8896	9565

James006 · Dec 14, 2018

Hi,

In cell B2 you can test following Array formula:

Code:

=INDEX(Sheet2!$B$1:$B$12,SMALL(IF(Sheet2!$A$1:$A$12=$A2,ROW($A$1:$A$12),""),Column()-1),1)

Hope this will help

AhoyNC · Dec 14, 2018

If you have Excel ver. 2010 or later try:

Excel Workbook

A

B

C

D

1

Account No

Payment

2

5031051019230

8551

3688

6854

3

5031008033030

7852

3552

9685

4

5031008002739

6585

7432

5

5031008031461

9548

8896

9565

Sheet1

Excel Workbook

A

B

1

Account No

Payment

2

5031051019230

8551

3

5031008033030

7852

4

5031008002739

6585

5

5031008031461

9548

6

5031008002739

7432

7

5031051019230

3688

8

5031008033030

3552

9

5031008031461

8896

10

5031008031461

9565

11

5031051019230

6854

12

5031008033030

9685

Sheet2

KRIXX · Dec 15, 2018

Thank you for reply. it works well. but if you can give the explanation of this formula syntax it will be helpful for me to use in other cases as well. i search the internet and found the generic syntax of this formula like below.

INDEX(return_range, SMALL(IF(lookup_range = lookup_value, ROW(return_range) - m, ""), COLUMN() - n))

Where:

m is the row number of the first cell in the return range minus 1.
n is the row number of the first formula cell minus 1.

but in below formula i am not able to understand the logic of this portion ,""),Column()-1),1)

=INDEX(Sheet2!$B$1:$B$12,SMALL(IF(Sheet2!$A$1:$A$12=$A2,ROW($A$1:$A$12),""),Column()-1),1)

if you can give explanation it will be very helpful to me. Thank you once again. cheers...

James006 · Dec 15, 2018

Glad to hear it is working as expected ...

The small function requires a k argument ...
which is basically : 1,2,3, etc ...

Since your formula is located in Column B ...
to get 1 .. you have Column()-1

Hope this clarifies

Aladin Akyurek · Dec 15, 2018

Book1

A

B

1

Account No

Payment

2

5031051019230

8551

3

5031008033030

7852

4

5031008002739

6585

5

5031008031461

9548

6

5031008002739

7432

7

5031051019230

3688

8

5031008033030

3552

9

5031008031461

8896

10

5031008031461

9565

11

5031051019230

6854

12

5031008033030

9685

Sheet2

Book1

A

B

C

D

1

Account No

Payment

2

5031051019230

8551

3688

6854

3

5031008033030

7852

3552

9685

4

5031008002739

6585

7432

5

5031008031461

9548

8896

9565

Sheet1

In B2 of Sheet1 control+shift+enter, not just enter, copy across, and down:

=IFERROR(INDEX(Sheet2!$B$2:$B$12,SMALL(IF(Sheet2!$A$2:$A$12=$A2,ROW(Sheet2!$B$2:$B$12)-ROW(Sheet2!$B$2)+1),COLUMNS($B2:B2))),"")

This "sublist" formula is provably robust (I'm afraid the version James006 forwards is not.). In the next version of Excel or so, the control+shift+enter confirmation for this class of formulas is no longer needed, so there will be no need for the array-processing AGGREGATE formulations.

Lookup The First, Second, Third, Or Nth Match Value In Excel

KRIXX

New Member

Excel Facts

James006

Well-known Member

AhoyNC

Well-known Member

KRIXX

New Member

James006

Well-known Member

Aladin Akyurek

MrExcel MVP

Similar threads

Forum statistics

Share this page

Lookup The First, Second, Third, Or Nth Match Value In Excel

KRIXX

New Member

Excel Facts

James006

Well-known Member

AhoyNC

Well-known Member

KRIXX

New Member

James006

Well-known Member

Aladin Akyurek

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock