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

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
164
Office Version
  1. 2013
Platform
  1. Windows
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
ABCDEFGHIJKLMN
1Pay TypeParent Membership NumberDO NOT DELETEPayment Statement numberPayment Received DateSurnameFirst NameAddressTownPost CodeMember TypeParent MemberAmountEMAIL ADDRESS
2666666
3SM0000112301/04/01DD Surname 1DD First Name 1DD Address 1DD Town 1DD Zip 1Y
4SM0000312401/04/01DD Surname 3DD First Name 3DD Address 3DD Town 3DD Zip 3Y
5SM0000512501/04/01DD Surname 5DD First Name 5DD Address 5DD Town 5DD Zip 5N
6RM00001350005/06/21Rec Surname 1Rec First Name 1Rec Address 1Rec Town 1Rec Zip 1Y
7RM00004350307/06/21Rec Surname 4Rec First Name 4Rec Address 4Rec Town 4Rec Zip 4Y
8RM00005350408/06/21Rec Surname 5Rec First Name 5Rec Address 5Rec Town 5Rec Zip 5N
9 
Juniors
Cell Formulas
RangeFormula
J2,D2:H2D2=COUNTIF(D3:D202,"<>")
I9I9=IF(ISBLANK(G9),"","Bolton")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A6:A202Cell Value="RM"textYES
M4:M202Cell Value=15textYES
N117,M1,M203:M1048576,M3Cell Value=15textYES
A203:A1048576,A1:A5Cell Value="RM"textYES


Edited Club Membership.xlsm
ABCDEFGHIJKLMN
1Pay TypeMembership NumberDO NOT DELETEPayment Statement numberPayment Received DateSurnameFirst NameAddressTownPost CodeMember TypeJunior ParentAmountEMAIL ADDRESS
223232323
3SM00001SM0000112301/04/01DD Surname 1DD First Name 1DD Address 1DD Town 1DD Zip 1SupportingYemail 1
4SM00002SM0000212301/04/01DD Surname 2DD First Name 2DD Address 2DD Town 2DD Zip 2SupportingYemail 2
5SM00003SM0000312401/04/01DD Surname 3DD First Name 3DD Address 3DD Town 3DD Zip 3SupportingYemail 3
6SM00004SM0000412401/04/01DD Surname 4DD First Name 4DD Address 4DD Town 4DD Zip 4SupportingYemail 4
7SM00005SM0000512501/04/01DD Surname 5DD First Name 5DD Address 5DD Town 5DD Zip 5SupportingNemail 5
8SM00006SM0000612501/04/01DD Surname 6DD First Name 6DD Address 6DD Town 6DD Zip 6SupportingYemail 6
9SM00007SM0000712501/04/01DD Surname 7DD First Name 7DD Address 7DD Town 7DD Zip 7SupportingYemail 7
10SM00008SM0000812501/04/01DD Surname 8DD First Name 8DD Address 8DD Town 8DD Zip 8SupportingYemail 8
11SM00009SM0000912501/04/01DD Surname 9DD First Name 9DD Address 9DD Town 9DD Zip 9SupportingYemail 9
12SM00010SM0001012501/04/01DD Surname 10DD First Name 10DD Address 10DD Town 10DD Zip 10SupportingYemail 10
DD Members
Cell Formulas
RangeFormula
D2,J2,G2:H2D2=COUNTIF(D3:D25,"<>")
C3:C12C3=CONCATENATE(A3,B3)
E3:E12E3=IF(LEN(D3)>0,DATE("20"&RIGHT(Formula!$B$1,1),4,1),"")
K3:K12K3=IF(ISBLANK(G3),"","Supporting")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
M1,M3:M1048576Cell Value=15textYES
A:ACell Value="RM"textYES
Cells with Data Validation
CellAllowCriteria
L3:L12ListY,N,


Edited Club Membership.xlsm
ABCDEFGHIJKLMN
1Pay TypeMembership NumberDO NOT DELETEReceipt NoPayment Received DateSurnameFirst NameAddressTownPost CodeMember TypeJunior ParentAmountEMAIL ADDRESS
2555555
3RM00001RM00001350005/06/21Rec Surname 1Rec First Name 1Rec Address 1Rec Town 1Rec Zip 1Supporting£15.00Email1
4RM00002RM00002350106/06/21Rec Surname 2Rec First Name 2Rec Address 2Rec Town 2Rec Zip 2Supporting£15.00Email2
5RM00003RM00003350206/06/21Rec Surname 3Rec First Name 3Rec Address 3Rec Town 3Rec Zip 3Supporting£15.00Email3
6RM00004RM00004350307/06/21Rec Surname 4Rec First Name 4Rec Address 4Rec Town 4Rec Zip 4Supporting£15.00Email4
7RM00005RM00005350408/06/21Rec Surname 5Rec First Name 5Rec Address 5Rec Town 5Rec Zip 5SupportingN£15.00Email5
8RM00006RM00006    
Receipt Members
Cell Formulas
RangeFormula
J2,D2:H2D2=COUNTIF(D3:D202,"<>")
K3:K8K3=IF(ISBLANK(G3),"","Supporting")
M3:M8M3=IF(ISBLANK(G3),"","£15.00")
C3:C8C3=CONCATENATE(A3,B3)
I8I8=IF(ISBLANK(G8),"","Bolton")
L8L8=IF(ISBLANK(G8),"","N")
Cells with Data Validation
CellAllowCriteria
L3:L8ListY,N
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,096
Office Version
  1. 2016
Platform
  1. Windows
To look at the left you can use INDEX and MATCH.
To switch other sheet you can use IFERROR.
 

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
164
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Aug 4, 2017
Messages
1,096
Office Version
  1. 2016
Platform
  1. Windows
something like this: ISERROR - INDEX sheetDD MATCH H3 in col Hon sheetDD - INDEX sheetRec MATCH H3 in col H on sheetRec
 

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
164
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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.
VBA Code:
=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
VBA Code:
=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

Well-known Member
Joined
Aug 4, 2017
Messages
1,096
Office Version
  1. 2016
Platform
  1. Windows
On the same way:
=INDEX(INDIRECT("'"&SheetList&"'!$C$3:$C$202"),MATCH(H3,INDIRECT("'"&SheetList&"'!$H$3:$H$202"),0))
 

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,096
Office Version
  1. 2016
Platform
  1. Windows
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)))
 
Solution
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,459
Messages
5,831,760
Members
430,088
Latest member
meagerd

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
Top