Vlookup or another method to look at the left for a value.

[Kayslover]

Hi All,

I have a sheet called Juniors that contains an address in column H3 onwards.

I would like to search sheets DD Members and Receipt Members (These sheets have been defined as a Named Range called SheetList).

The address in Sheets DD Members and Receipt Members also exists in column H3 onwards. (You could have multiple addresses that match).

Is it possible to bring back data held in corresponding cell in columns D and E into sheet Juniors from sheets DD Members and Receipt Members when the address matches?

E.G.

Sheet Juniors, address in H3, should return:-
SM00001 in C3, 123 in D3, 01/04/21 in E3 and Y in L3
Sheet Juniors, address in H4, should return:-
SM00003 in C4, 124 in D4, 01/04/21 in E4 and Y in L4
Sheet Juniors, address in H5, should return:-
SM00005 in C5, 124 in D5, 01/04/21 in E5 and N in L5

Sheet Juniors, address in H6, should return:-
RM00001 in C6, 3500 in D6, 05/06/21 in E6 and Y in L6
Sheet Juniors, address in H7, should return:-
RM00004 in C7, 3503 in D7, 07/06/21 in E7 and Y in L7
Sheet Juniors, address in H8, should return:-
RM00005 in C8, 3504 in D8, 08/06/21 in E8 and N in L8

Edited Club Membership.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Pay Type	Parent Membership Number	DO NOT DELETE	Payment Statement number	Payment Received Date	Surname	First Name	Address	Town	Post Code	Member Type	Parent Member	Amount	EMAIL ADDRESS
2				6	6	6	6	6		6
3			SM00001	123	01/04/01	DD Surname 1	DD First Name 1	DD Address 1	DD Town 1	DD Zip 1		Y
4			SM00003	124	01/04/01	DD Surname 3	DD First Name 3	DD Address 3	DD Town 3	DD Zip 3		Y
5			SM00005	125	01/04/01	DD Surname 5	DD First Name 5	DD Address 5	DD Town 5	DD Zip 5		N
6			RM00001	3500	05/06/21	Rec Surname 1	Rec First Name 1	Rec Address 1	Rec Town 1	Rec Zip 1		Y
7			RM00004	3503	07/06/21	Rec Surname 4	Rec First Name 4	Rec Address 4	Rec Town 4	Rec Zip 4		Y
8			RM00005	3504	08/06/21	Rec Surname 5	Rec First Name 5	Rec Address 5	Rec Town 5	Rec Zip 5		N
9
Juniors

Cell Formulas
Range	Formula
J2,D2:H2	D2	=COUNTIF(D3:D202,"<>")
I9	I9	=IF(ISBLANK(G9),"","Bolton")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A6:A202	Cell Value	="RM"	text	YES
M4:M202	Cell Value	=15	text	YES
N117,M1,M203:M1048576,M3	Cell Value	=15	text	YES
A203:A1048576,A1:A5	Cell Value	="RM"	text	YES

Edited Club Membership.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Pay Type	Membership Number	DO NOT DELETE	Payment Statement number	Payment Received Date	Surname	First Name	Address	Town	Post Code	Member Type	Junior Parent	Amount	EMAIL ADDRESS
2				23			23	23		23
3	SM	00001	SM00001	123	01/04/01	DD Surname 1	DD First Name 1	DD Address 1	DD Town 1	DD Zip 1	Supporting	Y		email 1
4	SM	00002	SM00002	123	01/04/01	DD Surname 2	DD First Name 2	DD Address 2	DD Town 2	DD Zip 2	Supporting	Y		email 2
5	SM	00003	SM00003	124	01/04/01	DD Surname 3	DD First Name 3	DD Address 3	DD Town 3	DD Zip 3	Supporting	Y		email 3
6	SM	00004	SM00004	124	01/04/01	DD Surname 4	DD First Name 4	DD Address 4	DD Town 4	DD Zip 4	Supporting	Y		email 4
7	SM	00005	SM00005	125	01/04/01	DD Surname 5	DD First Name 5	DD Address 5	DD Town 5	DD Zip 5	Supporting	N		email 5
8	SM	00006	SM00006	125	01/04/01	DD Surname 6	DD First Name 6	DD Address 6	DD Town 6	DD Zip 6	Supporting	Y		email 6
9	SM	00007	SM00007	125	01/04/01	DD Surname 7	DD First Name 7	DD Address 7	DD Town 7	DD Zip 7	Supporting	Y		email 7
10	SM	00008	SM00008	125	01/04/01	DD Surname 8	DD First Name 8	DD Address 8	DD Town 8	DD Zip 8	Supporting	Y		email 8
11	SM	00009	SM00009	125	01/04/01	DD Surname 9	DD First Name 9	DD Address 9	DD Town 9	DD Zip 9	Supporting	Y		email 9
12	SM	00010	SM00010	125	01/04/01	DD Surname 10	DD First Name 10	DD Address 10	DD Town 10	DD Zip 10	Supporting	Y		email 10
DD Members

Cell Formulas
Range	Formula
D2,J2,G2:H2	D2	=COUNTIF(D3:D25,"<>")
C3:C12	C3	=CONCATENATE(A3,B3)
E3:E12	E3	=IF(LEN(D3)>0,DATE("20"&RIGHT(Formula!$B$1,1),4,1),"")
K3:K12	K3	=IF(ISBLANK(G3),"","Supporting")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
M1,M3:M1048576	Cell Value	=15	text	YES
A:A	Cell Value	="RM"	text	YES

Cells with Data Validation
Cell	Allow	Criteria
L3:L12	List	Y,N,

Edited Club Membership.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Pay Type	Membership Number	DO NOT DELETE	Receipt No	Payment Received Date	Surname	First Name	Address	Town	Post Code	Member Type	Junior Parent	Amount	EMAIL ADDRESS
2				5	5	5	5	5		5
3	RM	00001	RM00001	3500	05/06/21	Rec Surname 1	Rec First Name 1	Rec Address 1	Rec Town 1	Rec Zip 1	Supporting		£15.00	Email1
4	RM	00002	RM00002	3501	06/06/21	Rec Surname 2	Rec First Name 2	Rec Address 2	Rec Town 2	Rec Zip 2	Supporting		£15.00	Email2
5	RM	00003	RM00003	3502	06/06/21	Rec Surname 3	Rec First Name 3	Rec Address 3	Rec Town 3	Rec Zip 3	Supporting		£15.00	Email3
6	RM	00004	RM00004	3503	07/06/21	Rec Surname 4	Rec First Name 4	Rec Address 4	Rec Town 4	Rec Zip 4	Supporting		£15.00	Email4
7	RM	00005	RM00005	3504	08/06/21	Rec Surname 5	Rec First Name 5	Rec Address 5	Rec Town 5	Rec Zip 5	Supporting	N	£15.00	Email5
8	RM	00006	RM00006
Receipt Members

Cell Formulas
Range	Formula
J2,D2:H2	D2	=COUNTIF(D3:D202,"<>")
K3:K8	K3	=IF(ISBLANK(G3),"","Supporting")
M3:M8	M3	=IF(ISBLANK(G3),"","£15.00")
C3:C8	C3	=CONCATENATE(A3,B3)
I8	I8	=IF(ISBLANK(G8),"","Bolton")
L8	L8	=IF(ISBLANK(G8),"","N")

Cells with Data Validation
Cell	Allow	Criteria
L3:L8	List	Y,N

 

[mart37]

To look at the left you can use INDEX and MATCH.
To switch other sheet you can use IFERROR.

 

[Kayslover]

Mart37,

Firstly thank you for your response.

Have been looking at the Internert for examples since you posted your recommendation. I cannot work out how to do insert the formulas at all.

 

[mart37]

something like this: ISERROR - INDEX sheetDD MATCH H3 in col Hon sheetDD - INDEX sheetRec MATCH H3 in col H on sheetRec

 

[Kayslover]

Mart37,

I have conjured the following formula in cell C3 in sheet Juniors and it seems to work by extracting the required information from column C in DD Members.

=INDEX('DD Members'!C:C,MATCH(H3,'DD Members'!$H:$H,0))

Once I have that information, I have inserted the following in sheet Juniors cell D3 and E3 (it is an array formula) and that gives me the required information from Column D and E in sheet DD Members

=IF(VLOOKUP($C3,INDIRECT("'"&INDEX(SheetList,MATCH(1,--(COUNTIF(INDIRECT("'"&SheetList&"'!$C$3:$L$202"),$C3)>0),0))&"'!$C$3:$L$202"),2,FALSE)=0," ",VLOOKUP($C3,INDIRECT("'"&INDEX(SheetList,MATCH(1,--(COUNTIF(INDIRECT("'"&SheetList&"'!$C$3:$L$202"),$C3)>0),0))&"'!$C$3:$L$202"),2,FALSE))

How can I change the formula in cell C3 in sheet Juniors to also look at Sheet Receipt Members.

The Array formula in D3 refers to SheetList which is a named range

 

[mart37]

On the same way:
=INDEX(INDIRECT("'"&SheetList&"'!$C$3:$C$202"),MATCH(H3,INDIRECT("'"&SheetList&"'!$H$3:$H$202"),0))

 

[mart37]

A solution with SheetList is the most beautiful. But I can't get that working.
Alternative solution:
=IFERROR(INDEX('DD Members'!C:C,MATCH(H3,'DD Members'!$H:$H,0)),INDEX('Receipt Members'!C:C,MATCH(H3,'Receipt Members'!$H:$H,0)))

 

[Kayslover]

Mart37,

Thanks, works perfectly. ? ?