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)
[TABLE="width: 582"]
<tbody>[TR]
[TD="class: xl67, width: 179"]Smith[/TD]
[TD="class: xl67, width: 142"]John A.[/TD]
[TD="class: xl69, width: 66"]67[/TD]
[TD="class: xl67, width: 195"]67 years,8 month(s), 21 Days[/TD]
[/TR]
</tbody>[/TABLE]

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

Ex.

Data (from Membership worksheet)
[TABLE="width: 1510"]
<tbody>[TR]
[TD]Joe[/TD]
[TD]Babcock[/TD]
[TD]307-191 Queen St.[/TD]
[TD]Anytown[/TD]
[TD]NB[/TD]
[TD]Canada[/TD]
[TD]A1A 1A3[/TD]
[TD]555-211-2123[/TD]
[TD]3email@gmail.com[/TD]
[TD]Post[/TD]
[TD]Carla (GF)[/TD]
[TD]September 29, 1953[/TD]
[TD]9[/TD]
[TD]64[/TD]
[TD]64 years,8 month(s), 18 Days[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Armstrong[/TD]
[TD]198 Court St.[/TD]
[TD]Anytown[/TD]
[TD]ON[/TD]
[TD]Canada[/TD]
[TD]A1A 1A1[/TD]
[TD]555-211-2121[/TD]
[TD]1email@gmail.com[/TD]
[TD]Post[/TD]
[TD]Irene[/TD]
[TD]March 17, 1959[/TD]
[TD]3[/TD]
[TD]59[/TD]
[TD]59 years,2 month(s), 30 Days[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Baumann[/TD]
[TD]Buffalo Rd. Group Box[/TD]
[TD]Anytown[/TD]
[TD]ON[/TD]
[TD]Canada[/TD]
[TD]A1A 1A5[/TD]
[TD]555-211-2125[/TD]
[TD]2email@gmail.com[/TD]
[TD]Post[/TD]
[TD][/TD]
[TD]September 25, 1963[/TD]
[TD]9[/TD]
[TD]54[/TD]
[TD]54 years,8 month(s), 22 Days[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Baker[/TD]
[TD]400 North Mill St.[/TD]
[TD]Anytown[/TD]
[TD]ON[/TD]
[TD]Canada[/TD]
[TD]A1A 1A4[/TD]
[TD]555-211-2124[/TD]
[TD]1email@gmail.com[/TD]
[TD]Email[/TD]
[TD]Pam[/TD]
[TD]January 27, 1976[/TD]
[TD]1[/TD]
[TD]42[/TD]
[TD]42 years,4 month(s), 20 Days[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Bedell[/TD]
[TD]101-7810 Ethel St.[/TD]
[TD]Anytown[/TD]
[TD]ON[/TD]
[TD]Canada[/TD]
[TD]A1A 1A7[/TD]
[TD]555-211-2127[/TD]
[TD]1email@gmail.com[/TD]
[TD]Email[/TD]
[TD]Trish[/TD]
[TD]May 27, 1987[/TD]
[TD]5[/TD]
[TD]31[/TD]
[TD]31 years,20 Days[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Bearss[/TD]
[TD]1490 Nigh Rd., RR#1[/TD]
[TD]Anytown[/TD]
[TD]ON[/TD]
[TD]Canada[/TD]
[TD]A1A 1A6[/TD]
[TD]555-211-2126[/TD]
[TD]3email@gmail.com[/TD]
[TD]Email[/TD]
[TD][/TD]
[TD]November 26, 1996[/TD]
[TD]11[/TD]
[TD]21[/TD]
[TD]21 years,6 month(s), 21 Days[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Arseneau[/TD]
[TD]3467 Murray St.[/TD]
[TD]Anytown[/TD]
[TD]ON[/TD]
[TD]Canada[/TD]
[TD]A1A 1A2[/TD]
[TD]555-211-2122[/TD]
[TD]2email@gmail.com[/TD]
[TD]Email[/TD]
[TD]Laura[/TD]
[TD]March 12, 1998[/TD]
[TD]3[/TD]
[TD]20[/TD]
[TD]20 years,3 month(s), 4 Days[/TD]
[/TR]
</tbody>[/TABLE]

Result (on Long Service worksheet)

[TABLE="width: 481"]
<tbody>[TR]
[TD]Last[/TD]
[TD]First[/TD]
[TD]Age[/TD]
[TD]Full Age[/TD]
[/TR]
[TR]
[TD]Babcock[/TD]
[TD]Joe[/TD]
[TD]64[/TD]
[TD]64 years,8 month(s), 18 Days[/TD]
[/TR]
[TR]
[TD]Armstrong[/TD]
[TD]Joe[/TD]
[TD]59[/TD]
[TD]59 years,2 month(s), 30 Days[/TD]
[/TR]
[TR]
[TD]Baumann[/TD]
[TD]Joe[/TD]
[TD]54[/TD]
[TD]54 years,8 month(s), 22 Days[/TD]
[/TR]
</tbody>[/TABLE]

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)))