Help with VLOOKUP

Kayslover

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

I have a VLOOKUP that looks like the following in Cells B2:B51 and C2:C51 in a sheet called Paid DD and the cells are formatted as General and Date:-

VBA Code:
=VLOOKUP(A2,'DD Members'!$C$3:$E$202,2,FALSE)
=VLOOKUP(A2,'DD Members'!$C$3:$E$202,3,FALSE)

DD Members is another sheet in the same workbook and the cells are formatted as General and Date in the sheet.

When a match is found, it bring the cell value as expected. However if the cell is empty in sheet DD Members, the result of the first VLOOKUP is 0 (in column B) and 00/01/00 in Column C.

Is it possible if the value to be returned by the VLOOKUP is empty, in Column B (sheet DD Members), then the VLOOKUP puts a blank in the Column B for the first VLOOKUP and the same for Column C.

The reason for the above is I have a COUNTA formula in sheet Paid DD, cell B52 (=COUNTA(B2:B51)) and C52 (=COUNTA(C2:C51)) which is returning the wrong count as the cells are not blank as they have a formula in them.

I have search the forum and even tried to
VBA Code:
=T(VLOOKUP(A2,'DD Members'!$C$3:$E$202,2,FALSE))
formula as suggested by Fluff in another post and that doesn’t work.

2020 Edited Membership.xlsm
ABCDEFGHIJKL
1Pay TypeMembership NumberDO NOT DELETE, REQUIRED in SHEET PAID DDPayment Statement numberPayment Received DateSurnameFirst NameAddressTownPost CodeMember TypeJunior Parent
2990000000
3SM00001SM0000130501/04/20
4SM00002SM0000230501/04/20
5SM00003SM0000324901/04/20
6SM00004SM0000430301/04/20
7SM00005SM0000530401/04/20
8SM00006SM0000630301/04/20
9SM00007SM0000724601/04/20
10SM00008SM0000830301/04/20
11SM00009SM0000930701/04/20
12SM00010SM0001024601/04/20
13SM00011SM0001130301/04/20
14SM00012SM0001230701/04/20
15SM00013SM0001330701/04/20
16SM00014SM0001430601/04/20
17SM00015SM0001530401/04/20
18SM00016SM0001630701/04/20
19SM00017SM00017
20SM00018SM00018
21SM00019SM0001932301/04/20
22SM00020SM0002030401/04/20
23SM00021SM0002130401/04/20
24SM00022SM0002230601/04/20
25SM00023SM0002330501/04/20
26SM00024SM00024
27SM00025SM0002525001/04/20
28SM00026SM0002625001/04/20
29SM00027SM0002730501/04/20
30SM00028SM0002824801/04/20
31SM00029SM0002930601/04/20
32SM00030SM0003030601/04/20
33SM00031SM0003130401/04/20
34SM00032SM0003230401/04/20
35SM00033SM0003325001/04/20
36SM00034SM0003430301/04/20
37SM00035SM0003530401/04/20
38SM00036SM0003630601/04/20
39SM00037SM0003730601/04/20
40SM00038SM0003830401/04/20
41SM00039SM0003930401/04/20
42SM00040SM0004030401/04/20
DD Members
Cell Formulas
RangeFormula
D2,F2:L2D2=COUNTA(D3:D152)
C3:C42C3=CONCATENATE(A3,B3)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A:ACell Value="RM"textYES


2020 Edited Membership.xlsm
ABCDEFGHIJKL
1M/Ship NoStatement NoPaid DateM/Ship NoStatement NoPaid DateM/Ship NoStatement NoPaid DateM/Ship NoStatement NoPaid Date
2SM0000130501/04/20SM0005130401/04/20SM0010130401/04/20SM00151000/01/00
3SM0000230501/04/20SM00052000/01/00SM0010230501/04/20SM00152000/01/00
4SM0000324901/04/20SM0005330501/04/20SM0010330601/04/20SM00153000/01/00
5SM0000430301/04/20SM00054000/01/00SM0010430601/04/20SM00154000/01/00
6SM0000530401/04/20SM0005530601/04/20SM0010530301/04/20SM00155000/01/00
7SM0000630301/04/20SM0005630601/04/20SM00106000/01/00SM00156000/01/00
8SM0000724601/04/20SM0005724801/04/20SM0010730601/04/20SM00157000/01/00
9SM0000830301/04/20SM0005830601/04/20SM00108000/01/00SM00158000/01/00
10SM0000930701/04/20SM0005930401/04/20SM00109000/01/00SM00159000/01/00
11SM0001024601/04/20SM0006030501/04/20SM0011030601/04/20SM00160000/01/00
12SM0001130301/04/20SM00061000/01/00SM00111297/32501/04/20SM00161000/01/00
13SM0001230701/04/20SM0006230401/04/20SM0011230401/04/20SM00162000/01/00
14SM0001330701/04/20SM0006330501/04/20SM0011330401/04/20SM00163000/01/00
15SM0001430601/04/20SM0006430501/04/20SM0011430401/04/20SM00164000/01/00
16SM0001530401/04/20SM0006530501/04/20SM00115000/01/00SM00165000/01/00
17SM0001630701/04/20SM0006630401/04/20SM0011630301/04/20SM00166000/01/00
18SM00017000/01/00SM00067000/01/00SM0011730601/04/20SM00167000/01/00
19SM00018000/01/00SM0006830501/04/20SM0011830601/04/20SM00168000/01/00
20SM0001932301/04/20SM0006930601/04/20SM00119000/01/00SM00169000/01/00
21SM0002030401/04/20SM0007030401/04/20SM0012030301/04/20SM00170000/01/00
22SM0002130401/04/20SM0007130501/04/20SM0012130601/04/20SM00171000/01/00
23SM0002230601/04/20SM0007230401/04/20SM00122000/01/00SM00172000/01/00
24SM0002330501/04/20SM00073000/01/00SM00123000/01/00SM00173000/01/00
25SM00024000/01/00SM0007430601/04/20SM0012430501/04/20SM00174000/01/00
26SM0002525001/04/20SM0007530601/04/20SM00125000/01/00SM00175000/01/00
27SM0002625001/04/20SM0007630301/04/20SM00126000/01/00SM00176000/01/00
28SM0002730501/04/20SM00077000/01/00SM00127000/01/00SM00177000/01/00
29SM0002824801/04/20SM0007830501/04/20SM0012830401/04/20SM00178000/01/00
30SM0002930601/04/20SM0007924901/04/20SM00129000/01/00SM00179000/01/00
31SM0003030601/04/20SM00080000/01/00SM00130000/01/00SM00180000/01/00
32SM0003130401/04/20SM00081000/01/00SM00131000/01/00SM00181000/01/00
33SM0003230401/04/20SM00082000/01/00SM00132000/01/00SM00182000/01/00
34SM0003325001/04/20SM0008324801/04/20SM00133000/01/00SM00183000/01/00
35SM0003430301/04/20SM0008430501/04/20SM00134000/01/00SM00184000/01/00
36SM0003530401/04/20SM0008530501/04/20SM00135000/01/00SM00185000/01/00
37SM0003630601/04/20SM0008630501/04/20SM00136000/01/00SM00186000/01/00
38SM0003730601/04/20SM0008730301/04/20SM00137000/01/00SM00187000/01/00
39SM0003830401/04/20SM0008824601/04/20SM00138000/01/00SM00188000/01/00
40SM0003930401/04/20SM0008930701/04/20SM00139000/01/00SM00189000/01/00
41SM0004030401/04/20SM0009030501/04/20SM00140000/01/00SM00190000/01/00
42SM0004130401/04/20SM0009130601/04/20SM00141000/01/00SM00191000/01/00
43SM00042000/01/00SM0009230501/04/20SM00142000/01/00SM00192000/01/00
44SM00043000/01/00SM00093000/01/00SM00143000/01/00SM00193000/01/00
45SM00044000/01/00SM0009430501/04/20SM00144000/01/00SM00194000/01/00
46SM0004530701/04/20SM0009530501/04/20SM00145000/01/00SM00195000/01/00
47SM0004630501/04/20SM0009630501/04/20SM00146000/01/00SM00196000/01/00
48SM00047000/01/00SM0009730501/04/20SM00147000/01/00SM00197000/01/00
49SM0004831501/04/20SM00098000/01/00SM00148000/01/00SM00198000/01/00
50SM00049000/01/00SM0009930601/04/20SM00149000/01/00SM00199000/01/00
51SM0005030301/04/20SM0010030601/04/20SM00150000/01/00SM00200000/01/00
52
53Column Count5050505050
54
55Total150
Paid DD
Cell Formulas
RangeFormula
K2:K51,H2:H51,E2:E51,B2:B51B2=VLOOKUP(A2,'DD Members'!$C$3:$E$202,2,FALSE)
L2:L51,I2:I51,F2:F51,C2:C51C2=VLOOKUP(A2,'DD Members'!$C$3:$E$202,3,FALSE)
J53,H53,E53,B53:C53B53=COUNTA(B2:B51)
B55B55=B53+E53+H53


I have had to delete data in sheet DD Members from Columns F onwards as it is P+C.

Any assistance offered will be appreciated.
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,575
Office Version
  1. 365
Platform
  1. MacOS
Try
=IF(VLOOKUP(A2,'DD Members'!$C$3:$E$202,2,FALSE)=0,"",VLOOKUP(A2,'DD Members'!$C$3:$E$202,2,FALSE))
 
Solution

Kayslover

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

Firstly, thanks for your time. Tried your suggestion and it works a treat. 👍 👍 :):)

Can you please explain your revised formula so that I can better understand it.
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,575
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

IF ( TEST VALUE, TRUE , FALSE)
The IF uses the above
if the test is TRUE then do the true part , if false do the false part

so
=IF(VLOOKUP(A2,'DD Members'!$C$3:$E$202,2,FALSE)=0,"",VLOOKUP(A2,'DD Members'!$C$3:$E$202,2,FALSE))

The TEST is
VLOOKUP(A2,'DD Members'!$C$3:$E$202,2,FALSE)=0
If that result in a zero
then do the TRUE part , which is
""
Otherwise do the false part , which is
VLOOKUP(A2,'DD Members'!$C$3:$E$202,2,FALSE)

But there are many otherwise to achieve the result, as suggested a countif()
 

A Durfani

Board Regular
Joined
Apr 12, 2019
Messages
228
Office Version
  1. 2013
Platform
  1. Windows
Hi All,

I have a VLOOKUP that looks like the following in Cells B2:B51 and C2:C51 in a sheet called Paid DD and the cells are formatted as General and Date:-

VBA Code:
=VLOOKUP(A2,'DD Members'!$C$3:$E$202,2,FALSE)
=VLOOKUP(A2,'DD Members'!$C$3:$E$202,3,FALSE)

DD Members is another sheet in the same workbook and the cells are formatted as General and Date in the sheet.

When a match is found, it bring the cell value as expected. However if the cell is empty in sheet DD Members, the result of the first VLOOKUP is 0 (in column B) and 00/01/00 in Column C.

Is it possible if the value to be returned by the VLOOKUP is empty, in Column B (sheet DD Members), then the VLOOKUP puts a blank in the Column B for the first VLOOKUP and the same for Column C.

The reason for the above is I have a COUNTA formula in sheet Paid DD, cell B52 (=COUNTA(B2:B51)) and C52 (=COUNTA(C2:C51)) which is returning the wrong count as the cells are not blank as they have a formula in them.

I have search the forum and even tried to
VBA Code:
=T(VLOOKUP(A2,'DD Members'!$C$3:$E$202,2,FALSE))
formula as suggested by Fluff in another post and that doesn’t work.

2020 Edited Membership.xlsm
ABCDEFGHIJKL
1Pay TypeMembership NumberDO NOT DELETE, REQUIRED in SHEET PAID DDPayment Statement numberPayment Received DateSurnameFirst NameAddressTownPost CodeMember TypeJunior Parent
2990000000
3SM00001SM0000130501/04/20
4SM00002SM0000230501/04/20
5SM00003SM0000324901/04/20
6SM00004SM0000430301/04/20
7SM00005SM0000530401/04/20
8SM00006SM0000630301/04/20
9SM00007SM0000724601/04/20
10SM00008SM0000830301/04/20
11SM00009SM0000930701/04/20
12SM00010SM0001024601/04/20
13SM00011SM0001130301/04/20
14SM00012SM0001230701/04/20
15SM00013SM0001330701/04/20
16SM00014SM0001430601/04/20
17SM00015SM0001530401/04/20
18SM00016SM0001630701/04/20
19SM00017SM00017
20SM00018SM00018
21SM00019SM0001932301/04/20
22SM00020SM0002030401/04/20
23SM00021SM0002130401/04/20
24SM00022SM0002230601/04/20
25SM00023SM0002330501/04/20
26SM00024SM00024
27SM00025SM0002525001/04/20
28SM00026SM0002625001/04/20
29SM00027SM0002730501/04/20
30SM00028SM0002824801/04/20
31SM00029SM0002930601/04/20
32SM00030SM0003030601/04/20
33SM00031SM0003130401/04/20
34SM00032SM0003230401/04/20
35SM00033SM0003325001/04/20
36SM00034SM0003430301/04/20
37SM00035SM0003530401/04/20
38SM00036SM0003630601/04/20
39SM00037SM0003730601/04/20
40SM00038SM0003830401/04/20
41SM00039SM0003930401/04/20
42SM00040SM0004030401/04/20
DD Members
Cell Formulas
RangeFormula
D2,F2:L2D2=COUNTA(D3:D152)
C3:C42C3=CONCATENATE(A3,B3)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A:ACell Value="RM"textYES


2020 Edited Membership.xlsm
ABCDEFGHIJKL
1M/Ship NoStatement NoPaid DateM/Ship NoStatement NoPaid DateM/Ship NoStatement NoPaid DateM/Ship NoStatement NoPaid Date
2SM0000130501/04/20SM0005130401/04/20SM0010130401/04/20SM00151000/01/00
3SM0000230501/04/20SM00052000/01/00SM0010230501/04/20SM00152000/01/00
4SM0000324901/04/20SM0005330501/04/20SM0010330601/04/20SM00153000/01/00
5SM0000430301/04/20SM00054000/01/00SM0010430601/04/20SM00154000/01/00
6SM0000530401/04/20SM0005530601/04/20SM0010530301/04/20SM00155000/01/00
7SM0000630301/04/20SM0005630601/04/20SM00106000/01/00SM00156000/01/00
8SM0000724601/04/20SM0005724801/04/20SM0010730601/04/20SM00157000/01/00
9SM0000830301/04/20SM0005830601/04/20SM00108000/01/00SM00158000/01/00
10SM0000930701/04/20SM0005930401/04/20SM00109000/01/00SM00159000/01/00
11SM0001024601/04/20SM0006030501/04/20SM0011030601/04/20SM00160000/01/00
12SM0001130301/04/20SM00061000/01/00SM00111297/32501/04/20SM00161000/01/00
13SM0001230701/04/20SM0006230401/04/20SM0011230401/04/20SM00162000/01/00
14SM0001330701/04/20SM0006330501/04/20SM0011330401/04/20SM00163000/01/00
15SM0001430601/04/20SM0006430501/04/20SM0011430401/04/20SM00164000/01/00
16SM0001530401/04/20SM0006530501/04/20SM00115000/01/00SM00165000/01/00
17SM0001630701/04/20SM0006630401/04/20SM0011630301/04/20SM00166000/01/00
18SM00017000/01/00SM00067000/01/00SM0011730601/04/20SM00167000/01/00
19SM00018000/01/00SM0006830501/04/20SM0011830601/04/20SM00168000/01/00
20SM0001932301/04/20SM0006930601/04/20SM00119000/01/00SM00169000/01/00
21SM0002030401/04/20SM0007030401/04/20SM0012030301/04/20SM00170000/01/00
22SM0002130401/04/20SM0007130501/04/20SM0012130601/04/20SM00171000/01/00
23SM0002230601/04/20SM0007230401/04/20SM00122000/01/00SM00172000/01/00
24SM0002330501/04/20SM00073000/01/00SM00123000/01/00SM00173000/01/00
25SM00024000/01/00SM0007430601/04/20SM0012430501/04/20SM00174000/01/00
26SM0002525001/04/20SM0007530601/04/20SM00125000/01/00SM00175000/01/00
27SM0002625001/04/20SM0007630301/04/20SM00126000/01/00SM00176000/01/00
28SM0002730501/04/20SM00077000/01/00SM00127000/01/00SM00177000/01/00
29SM0002824801/04/20SM0007830501/04/20SM0012830401/04/20SM00178000/01/00
30SM0002930601/04/20SM0007924901/04/20SM00129000/01/00SM00179000/01/00
31SM0003030601/04/20SM00080000/01/00SM00130000/01/00SM00180000/01/00
32SM0003130401/04/20SM00081000/01/00SM00131000/01/00SM00181000/01/00
33SM0003230401/04/20SM00082000/01/00SM00132000/01/00SM00182000/01/00
34SM0003325001/04/20SM0008324801/04/20SM00133000/01/00SM00183000/01/00
35SM0003430301/04/20SM0008430501/04/20SM00134000/01/00SM00184000/01/00
36SM0003530401/04/20SM0008530501/04/20SM00135000/01/00SM00185000/01/00
37SM0003630601/04/20SM0008630501/04/20SM00136000/01/00SM00186000/01/00
38SM0003730601/04/20SM0008730301/04/20SM00137000/01/00SM00187000/01/00
39SM0003830401/04/20SM0008824601/04/20SM00138000/01/00SM00188000/01/00
40SM0003930401/04/20SM0008930701/04/20SM00139000/01/00SM00189000/01/00
41SM0004030401/04/20SM0009030501/04/20SM00140000/01/00SM00190000/01/00
42SM0004130401/04/20SM0009130601/04/20SM00141000/01/00SM00191000/01/00
43SM00042000/01/00SM0009230501/04/20SM00142000/01/00SM00192000/01/00
44SM00043000/01/00SM00093000/01/00SM00143000/01/00SM00193000/01/00
45SM00044000/01/00SM0009430501/04/20SM00144000/01/00SM00194000/01/00
46SM0004530701/04/20SM0009530501/04/20SM00145000/01/00SM00195000/01/00
47SM0004630501/04/20SM0009630501/04/20SM00146000/01/00SM00196000/01/00
48SM00047000/01/00SM0009730501/04/20SM00147000/01/00SM00197000/01/00
49SM0004831501/04/20SM00098000/01/00SM00148000/01/00SM00198000/01/00
50SM00049000/01/00SM0009930601/04/20SM00149000/01/00SM00199000/01/00
51SM0005030301/04/20SM0010030601/04/20SM00150000/01/00SM00200000/01/00
52
53Column Count5050505050
54
55Total150
Paid DD
Cell Formulas
RangeFormula
K2:K51,H2:H51,E2:E51,B2:B51B2=VLOOKUP(A2,'DD Members'!$C$3:$E$202,2,FALSE)
L2:L51,I2:I51,F2:F51,C2:C51C2=VLOOKUP(A2,'DD Members'!$C$3:$E$202,3,FALSE)
J53,H53,E53,B53:C53B53=COUNTA(B2:B51)
B55B55=B53+E53+H53


I have had to delete data in sheet DD Members from Columns F onwards as it is P+C.

Any assistance offered will be appreciated.

try
Excel Formula:
=COUNTIF(B2:B51,">"&0)
instead
Excel Formula:
=COUNTA(B2:B51)
 

Kayslover

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

ADVERTISEMENT

Etaf,

Thank you for the explanation and your time.

Mart37,

Tried out your suggestion and that works. I used
VBA Code:
=COUNTIF(B2:B51,">0")
Thank you for your time.
 

Kayslover

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

I don't get the correct result with =COUNTA(B2:B51) due to formulas in all the rows.

=COUNTIF(B2:B51,">"0), give me the correct result.

Thanks you for your time.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,120
Messages
5,622,855
Members
415,934
Latest member
adstocking

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