Index Match function using non-contiguous columns in table

[ou812eh]

I'm looking to return values, numeric, and text from an lookup (index match) from non-contiguous columns in a table.

Ex. if Age is greater than 60 return:Last Name | First Name | Age | Full Age (yrs, months, days)

Smith

John A.

67

67 years,8 month(s), 21 Days

<tbody>
</tbody>

This data would be pulled from another worksheet (table) in the same workbook.

Ex.

Data (from Membership worksheet)

Joe

Babcock

307-191 Queen St.

Anytown

NB

Canada

A1A 1A3

555-211-2123

3email@gmail.com

Post

Carla (GF)

September 29, 1953

9

64

64 years,8 month(s), 18 Days

Joe

Armstrong

198 Court St.

Anytown

ON

Canada

A1A 1A1

555-211-2121

1email@gmail.com

Post

Irene

March 17, 1959

3

59

59 years,2 month(s), 30 Days

Joe

Baumann

Buffalo Rd. Group Box

Anytown

ON

Canada

A1A 1A5

555-211-2125

2email@gmail.com

Post

September 25, 1963

9

54

54 years,8 month(s), 22 Days

Joe

Baker

400 North Mill St.

Anytown

ON

Canada

A1A 1A4

555-211-2124

1email@gmail.com

Email

Pam

January 27, 1976

1

42

42 years,4 month(s), 20 Days

Joe

Bedell

101-7810 Ethel St.

Anytown

ON

Canada

A1A 1A7

555-211-2127

1email@gmail.com

Email

Trish

May 27, 1987

5

31

31 years,20 Days

Joe

Bearss

1490 Nigh Rd., RR#1

Anytown

ON

Canada

A1A 1A6

555-211-2126

3email@gmail.com

Email

November 26, 1996

11

21

21 years,6 month(s), 21 Days

Joe

Arseneau

3467 Murray St.

Anytown

ON

Canada

A1A 1A2

555-211-2122

2email@gmail.com

Email

Laura

March 12, 1998

3

20

20 years,3 month(s), 4 Days

<tbody>
</tbody>

Result (on Long Service worksheet)

Last	First	Age	Full Age
Babcock	Joe	64	64 years,8 month(s), 18 Days
Armstrong	Joe	59	59 years,2 month(s), 30 Days
Baumann	Joe	54	54 years,8 month(s), 22 Days

<tbody>
</tbody>

I had hoped to attach the dummy workbook.
Thank you.

 

[Aladin Akyurek]

What is given in Long Service and what must be retrieved from Data?

 

[ou812eh]

What is given in Long Service and what must be retrieved from Data?

Aladin,
I don't know that I understand your question.


I'm looking to return values, numeric, and text from an lookup (index match) from non-contiguous columns in a table. if Age is greater than X (60) then i want the corresponding information - first name, last name, age and full age.

From Data (Membership)
if Age is greater than 60 return:Last Name | First Name | Age | Full Age (yrs, months, days)

 

[Saba Sabaratnam]

Hi Ou812eh,

Copy the data into A2 to O8 to understand the formula. Then you can modify range references to suit your needs.

Copy Last Name, First Name, Age and Full age in A10, B10, C10 and D10

Enter the following array formula in A11 by pressing Shift + Control + Enter

=IFERROR(INDEX($B$2:$B$8,MATCH(SMALL((IF($N$2:$N$8>=60,ROW($N$2:$N$8)-1)),ROW(A11)-10),IF($N$2:$N$8>=60,ROW($N$2:$N$8)-1),0)),"")

Enter the following array formula in B11

=IFERROR(INDEX($A$2:$A$8,MATCH(SMALL((IF($N$2:$N$8>=60,ROW($N$2:$N$8)-1)),ROW(A11)-10),IF($N$2:$N$8>=60,ROW($N$2:$N$8)-1),0)),"")

Enter the following array formula in C11

=IFERROR(INDEX($N$2:$N$8,MATCH(SMALL((IF($N$2:$N$8>=60,ROW($N$2:$N$8)-1)),ROW(A11)-10),IF($N$2:$N$8>=60,ROW($N$2:$N$8)-1),0)),"")

Enter the last array formula in D11

=IFERROR(INDEX($O$2:$O$8,MATCH(SMALL((IF($N$2:$N$8>=60,ROW($N$2:$N$8)-1)),ROW(A11)-10),IF($N$2:$N$8>=60,ROW($N$2:$N$8)-1),0)),"")

Let us know how you go.

Kind regards

Saba

 

[Aladin Akyurek]

Aladin,
I don't know that I understand your question.


I'm looking to return values, numeric, and text from an lookup (index match) from non-contiguous columns in a table. if Age is greater than X (60) then i want the corresponding information - first name, last name, age and full age.

From Data (Membership)
if Age is greater than 60 return:Last Name | First Name | Age | Full Age (yrs, months, days)

Book1
	A	B	N	O
1	first name	last name	age	full age
2	Joe	Babcock	64	64 years,8 month(s), 18 Days
3	Joe	Armstrong	59	59 years,2 month(s), 30 Days
4	Joe	Baumann	54	54 years,8 month(s), 22 Days
5	Joe	Baker	42	42 years,4 month(s), 20 Days
6	Joe	Bedell	31	31 years,20 Days
7	Joe	Bearss	21	21 years,6 month(s), 21 Days
8	Joe	Arseneau	20	20 years,3 month(s), 4 Days
Data

Book1
	A	B	C	D	E
1	50	3
2	idx	last name	first name	age	full age
3	1	Babcock	Joe	64	64 years,8 month(s), 18 Days
4	2	Armstrong	Joe	59	59 years,2 month(s), 30 Days
5	3	Baumann	Joe	54	54 years,8 month(s), 22 Days
6
Long Service

In B1 just enter:

=COUNTIFS(Data!N2:N8,">"&A1)

In A3 control+shift+enter, not just enter, and copy down:

=IF(ROWS($A$3:A3)>$B$1,"",SMALL(IF(Data!$N$2:$N$8>$B$1,ROW(Data!$A$2:$O$8)-ROW(INDEX(Data!$A$2:$O$8,1,1))+1),ROWS($A$3:A3)))

In B3 just enter and copy down:

=IF($A3="","",INDEX(Data!$A$2:$O$8,$A3,MATCH(B$2,Data!$A$1:$O$1,0)))