Help with Vlookup.

K

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
168
Office Version
  1. 2013
Platform
  1. Windows
Hi All,

I have the following formula that is returning #N/A, when I am sure that it should return a Value.

The formula is an array formula and I entered it in the cell with CTR+SHIFT+ENTER

VBA Code: 
=VLOOKUP($C3,INDIRECT("'"&INDEX(SheetList,MATCH(1,--(COUNTIF(INDIRECT("'"&SheetList&"'!,$C$3:$L$202"),$C3)>0),0))&"'!!,$C$3:$L$202"),10,FALSE)
The above formula exists in cells in Column L in sheet called Juniors

In sheet called Formula, I have created a named range called SheetList for the 2 sheet names which exist in workbook. The sheet names are DD Members and Receipt Members.

What I would like to happen is for the formulas to look at sheets DD Members and Receipt Members and return the value from Column L (headed Junior Parent).

Currently it is returning a #N/A

Club Membership.xlsm
ABCDEFGHIJKLMNOPQR
1Pay TypeParent Membership NumberDO NOT DELETEPayment Statement numberPayment Received DateSurnameFirst NameAddressTownPost CodeMember TypeParent MemberAmountEMAIL ADDRESSEmail Sent Junior Membership Number
2001111
3SM00001SM00001Junior Surname 1Junior Name 1Junior Address 1Town 1Zip 1#N/AJM00001JM00001
4SM00002SM00002JM00002JM00002
5SM00003SM00003JM00003JM00003
6SM00004SM00004JM00004JM00004
7SM00005SM00005JM00005JM00005
8SM00006SM00006JM00006JM00006
9SM00007SM00007JM00007JM00007
10SM00008SM00008JM00008JM00008
11SM00009SM00009JM00009JM00009
12SM00010SM00010JM00010JM00010
13SM00011SM00011JM00011JM00011
14SM00012SM00012JM00012JM00012
15SM00013SM00013JM00013JM00013
16SM00014SM00014JM00014JM00014
17SM00015SM00015JM00015JM00015
18SM00016SM00016JM00016JM00016
19SM00017SM00017JM00017JM00017
20SM00018SM00018JM00018JM00018
21SM00019SM00019JM00019JM00019
22SM00020SM00020JM00020JM00020
23SM00021SM00021JM00021JM00021
24SM00022SM00022JM00022JM00022
Juniors
Cell Formulas
RangeFormula
J2,D2:H2D2=COUNTIF(D3:D24,"<>")
L3L3=VLOOKUP($C3,INDIRECT("'"&INDEX(SheetList,MATCH(1,--(COUNTIF(INDIRECT("'"&SheetList&"'!,$C$3:$L$202"),$C3)>0),0))&"'!!,$C$3:$L$202"),10,FALSE)
R3:R24,C3:C24C3=CONCATENATE(A3,B3)
Press CTRL+SHIFT+ENTER to enter array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A4:A24Cell Value="RM"textYES
M4:M24Cell Value=15textYES
M1,M3,M25:M1048576Cell Value=15textYES
A1:A3,A25:A1048576Cell Value="RM"textYES


Club Membership.xlsm
ABCDE
1Processing Year2021Sheet Names
2
3
4DD Members
5Receipt Members
Formula
Cell Formulas
RangeFormula
D4D4='DD Members'!AG1
D5D5='Receipt Members'!AG1


Club Membership.xlsm
ABCDEFGHIJKLMNO
1Pay TypeMembership NumberDO NOT DELETEPayment Statement numberPayment Received DateSurnameFirst NameAddressTownPost CodeMember TypeJunior ParentAmountEMAIL ADDRESSEmail Sent
20181818
3SM00001SM00001 Surname 1First Name 1Address 1Town 1Zip 1SupportingN£10.00
4SM00002SM00002 Surname 2First Name 2Address 2Town 2Zip 2SupportingN£10.00
5SM00003SM00003 Surname 3First Name 3Address 3Town 3Zip 3SupportingY£10.00
6SM00004SM00004 Cancelled  
7SM00005SM00005 Surname 5First Name 5Address 5Town 5Zip 5SupportingN£10.00
8SM00006SM00006 Surname 6First Name 6Address 6Town 6Zip 6SupportingN£10.00
9SM00007SM00007 Surname 7First Name 7Address 7Town 7Zip 7SupportingY£10.00
10SM00008SM00008 Cancelled  
11SM00009SM00009 Surname 9First Name 9Address 9Town 9Zip 9SupportingN£10.00
12SM00010SM00010 Surname 10First Name 10Address 10Town 10Zip 10SupportingN£10.00
13SM00011SM00011 Surname 11First Name 11Address 11Town 11Zip 11SupportingY£10.00
14SM00012SM00012 Surname 12First Name 12Address 12Town 12Zip 12SupportingN£10.00
15SM00013SM00013 Surname 13First Name 13Address 13Town 13Zip 13SupportingN£10.00
16SM00014SM00014 Surname 14First Name 14Address 14Town 14Zip 14SupportingY£10.00
17SM00015SM00015 Surname 15First Name 15Address 15Town 15Zip 15SupportingN£10.00
18SM00016SM00016 Surname 16First Name 16Address 16Town 16Zip 16SupportingN£10.00
19SM00017SM00017 Surname 17First Name 17Address 17Town 17Zip 17SupportingY£10.00
20SM00018SM00018 Surname 18First Name 18Address 18Town 18Zip 18SupportingN£10.00
21SM00019SM00019 Surname 19First Name 19Address 19Town 19Zip 19SupportingN£10.00
22SM00020SM00020 Surname 20First Name 20Address 20Town 20Zip 20SupportingN£10.00
DD Members
Cell Formulas
RangeFormula
D2,J2,G2:H2D2=COUNTIF(D3:D22,"<>")
C3:C22C3=CONCATENATE(A3,B3)
E3:E22E3=IF(LEN(D3)>0,DATE("20"&RIGHT($Q$1,2),4,1),"")
K3:K22K3=IF(ISBLANK(G3),"","Supporting")
M3:M22M3=IF(ISBLANK(G3),"","£10.00")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
M1,M3:M1048576Cell Value=15textYES
A:ACell Value="RM"textYES


Club Membership.xlsm
ABCDEFGHIJKLMNO
1Pay TypeMembership NumberDO NOT DELETEDO NOT DELETEPayment Received DateSurnameFirst NameAddressTownPost CodeMember TypeJunior ParentAmountEMAIL ADDRESSEmail Sent
200666206
3RM00001RM00001Surname 1First Name 1Address 1Town 1Zip 1SupportingN£15.00email 1
4RM00002RM00002Surname 2First Name 2Address 2Town 2Zip 2SupportingN£15.00email 2
5RM00003RM00003Surname 3First Name 3Address 3Town 3Zip 3SupportingY£15.00email 3
6RM00004RM00004Surname 4First Name 4Address 4Town 4Zip 4SupportingY£15.00email 4
7RM00005RM00005Surname 5First Name 5Address 5Town 5Zip 5SupportingN£15.00email 5
8RM00006RM00006Surname 6First Name 6Address 6Town 6Zip 6SupportingN£15.00email 6
9RM00007RM00007    
10RM00008RM00008    
11RM00009RM00009    
12RM00010RM00010    
13RM00011RM00011    
14RM00012RM00012    
15RM00013RM00013    
16RM00014RM00014    
17RM00015RM00015    
18RM00016RM00016    
19RM00017RM00017    
20RM00018RM00018    
21RM00019RM00019    
22RM00020RM00020    
Receipt Members
Cell Formulas
RangeFormula
D2:J2D2=COUNTIF(D3:D22,"<>")
K3:K22K3=IF(ISBLANK(G3),"","Supporting")
M3:M22M3=IF(ISBLANK(G3),"","£15.00")
L9:L22L9=IF(ISBLANK(G9),"","N")
C3:C22C3=CONCATENATE(A3,B3)
I9:I22I9=IF(ISBLANK(G9),"","Bolton")


Any help you can offer will be appreciated.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
Kayslover said:
In sheet called Formula, I have created a named range called SheetList for the 2 sheet name
Click to expand...
Can you please post what the name manager says SheetList refers to (or if you didn't have it checked when you last posted check the Named Ranges check box in XL2BB and post that).

Also what do you have in DD Members cell AG1 and Receipt Members cell AG1 ?
 
Last edited:
Upvote 0
I would have expected a #REF error with the invalid range references in your formula, you had an extra exclamation mark at one point, along with a couple of commas that shouldn't be there.

See if this fixes it before we try digging any deeper into the dark depths of that formula.
Excel Formula: 
=VLOOKUP($C3,INDIRECT("'"&INDEX(SheetList,MATCH(1,--(COUNTIF(INDIRECT("'"&SheetList&"'!$C$3:$L$202"),$C3)>0),0))&"'!$C$3:$L$202"),10,FALSE)
 
Upvote 0
Solution
Mark858,

As requested the Named manager says refers to
VBA Code: 
=Formula!$D$4:$D$5

Jasonb75,

Changed the formula to what you suggested and BINGO.

If Cell L3 to L202 is a Blank OR a Space, then the result in Juniors is that it puts a 0.

Any way the formula can be changed so that it displays exactly what is in the Cell in sheets DD Members and Receipt Members?
 
Upvote 0
Hi All,

Changed Jasonb75 formula to have a an IF, and I got what I required.

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

Thank you to Mark858 and Jasonb75 for their assistance.
 
Upvote 0
Doubling up on the formula means double the inefficiency, this should do what you need.
Excel Formula: 
=IFERROR(VLOOKUP($C5,INDIRECT("'"&INDEX(SheetList,MATCH(1,--(COUNTIF(INDIRECT("'"&SheetList&"'!$C$3:$L$202"),$C5)>0),0))&"'!$C$3:$L$202"),10,FALSE)&"","")
Also, try and get out of the habit of using " " for a blank cell, "" is the common 'best practice' and is usually assumed if you ask for a formula to work with blank / empty cells. The sneaky space has ruined many good formulas in the past.
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Index and match formula that ignores dynamic value
2
Replies
14
Views
392
FormR
FormR
N
  • Question
Count - specific consecutive order
Replies
6
Views
511
Peter_SSs
Peter_SSs
SaraWitch
  • Solved
Join text and return dates where there is one but ignore if empty cells
2
Replies
12
Views
545
SaraWitch
SaraWitch
Chris_010101
  • Solved
Complicated SUMIFS/COUNTIFS Formulas Required
Replies
5
Views
512
etaf
etaf
M
  • Question
Lookup based on Person in top row
Replies
1
Views
88
awoohaw
awoohaw

Forum statistics

Threads
1,214,918
Messages
6,122,257
Members
449,075
Latest member
staticfluids

Share this page

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
Back
Top