If statement not working

P

pastorkc

Board Regular
Joined
Jan 29, 2020
Messages
125
Office Version
  1. 2010
Platform
  1. Windows
I am at a lost as to why this If statement is just coming back blank.

=IF(ISNUMBER(MATCH(A2, 'Vendor Trans Hist-Macro'!A:A, 0)), IF(E2 = VLOOKUP(A2, 'Vendor Trans Hist-Macro'!A:D, 4, 0), VLOOKUP(A2, 'Vendor Trans Hist-Macro'!A:D, 4, 0) + 1, ""), "")

Any help would be appreciated.

This is being placed in a cell on a different worksheet within the workbook looking at this worksheet to match.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
Very difficult to answer your question, as we don't know what the formula is returning, or what data is in those cells.
Can you post some sample data from both sheets (along with expected result) using the XL2BB add-in
 
Upvote 0
The formula 'works' for me.
It will return "" if either
  • A2 is not found in column A of 'Vendor Trans Hist-Macro' (see row 3 below), or
  • A2 is found in column A of 'Vendor Trans Hist-Macro', but E2 is not the same as the value in column D on 'Vendor Trans Hist-Macro' on the same row as the A2 value first appears in column A of 'Vendor Trans Hist-Macro' (row 4)
Here is my sample data with the formula.

pastorkc_1.xlsm
ABCD
1
2a6
Vendor Trans Hist-Macro


pastorkc_1.xlsm
ABCDEF
1
2a67
3z3 
4a4 
Sheet1
Cell Formulas
RangeFormula
F2:F4F2=IF(ISNUMBER(MATCH(A2, 'Vendor Trans Hist-Macro'!A:A, 0)), IF(E2 = VLOOKUP(A2, 'Vendor Trans Hist-Macro'!A:D, 4, 0), VLOOKUP(A2, 'Vendor Trans Hist-Macro'!A:D, 4, 0) + 1, ""), "")
 
Upvote 0
Q40 Invoice-Working.xlsm
ABCDEF
1Vendor NameInvoice No.TransactionTrans No.DateAmount
212-Point Sign WorksINV-24828InvoiceINV-248286/30/231,455.37
312-Point Sign WorksPayment217637/28/23-1,455.37
4Air Conditioning Service, Inc.i10001513Invoicei100015135/24/23175.00
5Air Conditioning Service, Inc.Payment215846/1/23-175.00
6All Pest Solutions, Inc.83980Invoice839804/4/23300.00
7All Pest Solutions, Inc.Payment215645/16/23-300.00
8All Pest Solutions, Inc.85302Invoice853025/4/2385.00
9All Pest Solutions, Inc.Payment215365/8/23-85.00
10All Pest Solutions, Inc.87204Invoice872046/16/2385.00
11All Pest Solutions, Inc.Payment216756/20/23-85.00
12Amplify Awards & Gifting48334Invoice483346/7/2360.75
13Amplify Awards & GiftingPayment216496/8/23-60.75
14Atmos Energy041323Invoice0413234/13/23212.48
15Atmos EnergyPayment215024/18/23-212.48
16Atmos Energy051123Invoice0511235/11/2354.25
17Atmos EnergyPayment215655/16/23-54.25
Vendor Trans Hist-Macro



Q40 Invoice-Working.xlsm
ABCDEF
1Vendor NameAccount IDAccount DescriptionDateReferenceDebit Amt
2Bankcard Center5090.01.Q40.D.GDues, Subs, Tuition (G&A)5/24/23052423998.16
3Brand Wise5020.02.Q40.D.GProfessional Services (G&A)6/7/2327373,765.00
4Cool Springs Mall, LLC5225.01.Q40.D.GParking Leases4/1/23040123750.00
5Cool Springs Mall, LLC5225.01.Q40.D.GParking Leases5/1/23050123750.00
6Cool Springs Mall, LLC5225.01.Q40.D.GParking Leases6/1/23060123750.00
7Crexie5090.01.Q40.D.GDues, Subs, Tuition (G&A)4/30/23Crexie250.00
8Crexie5090.01.Q40.D.GDues, Subs, Tuition (G&A)5/31/23Crexie125.00
9Crexie5090.01.Q40.D.GDues, Subs, Tuition (G&A)6/30/23Crexie125.00
10Indirect Expenditure9920.99.Q40.D.GIndirect Cost Recovered4/30/23Indirect Expenditure1,854.42
11Indirect Expenditure9920.99.Q40.D.GIndirect Cost Recovered5/31/23Indirect Expenditure2,599.99
12Indirect Expenditure9920.99.Q40.D.GIndirect Cost Recovered6/30/23Indirect Expenditure2,843.75
13Journal Communicatio5090.13.Q40.D.GPrint Advertising (G&A)4/30/23Journal Communicatio254.17
GL Q40


I am trying to get it to look at column a of the GL Q40 worksheet and match to column A of the second worksheet, if there is a match found then look at column E of the first worksheet and match to column d of the second worksheet if a match id found then take the value of column d +1 because I need the payment reference number to be returned to the cell on worksheet 1 and continue this down until the last row in worksheet 1
 
Upvote 0
None of those Vendor Names on 'GL Q40' exist in column A of the other worksheet, so as explained above, that is why "" is returned.
 
Upvote 0
Peter_SSs said:
None of those Vendor Names on 'GL Q40' exist in column A of the other worksheet, so as explained above, that is why "" is returned.
Click to expand...
This list on the second sheet is much longer and does include the names on the first sheet. I didn't think anyone wanted the whole worksheets uploaded.
 
Upvote 0
We don't want the whole sheet, but we need some sample data of yours that should be returning results but is not so that we can investigate why.
 
Upvote 0
Peter_SSs said:
The formula 'works' for me.
It will return "" if either
  • A2 is not found in column A of 'Vendor Trans Hist-Macro' (see row 3 below), or
  • A2 is found in column A of 'Vendor Trans Hist-Macro', but E2 is not the same as the value in column D on 'Vendor Trans Hist-Macro' on the same row as the A2 value first appears in column A of 'Vendor Trans Hist-Macro' (row 4)
Here is my sample data with the formula.

pastorkc_1.xlsm
ABCD
1
2a6
Vendor Trans Hist-Macro


pastorkc_1.xlsm
ABCDEF
1
2a67
3z3 
4a4 
Sheet1
Cell Formulas
RangeFormula
F2:F4F2=IF(ISNUMBER(MATCH(A2, 'Vendor Trans Hist-Macro'!A:A, 0)), IF(E2 = VLOOKUP(A2, 'Vendor Trans Hist-Macro'!A:D, 4, 0), VLOOKUP(A2, 'Vendor Trans Hist-Macro'!A:D, 4, 0) + 1, ""), "")
Click to expand...
How do I get it to return the value of worksheet Vendor Trans Hist-Macro column d +1 instead of ""?
 
Upvote 0
Q40 Invoice-Working.xlsm
ABCDEF
1Vendor NameAccount IDAccount DescriptionDateReferenceDebit Amt
2Bankcard Center5090.01.Q40.D.GDues, Subs, Tuition (G&A)5/24/23052423998.16
3Brand Wise5020.02.Q40.D.GProfessional Services (G&A)6/7/2327373,765.00
4Cool Springs Mall, LLC5225.01.Q40.D.GParking Leases4/1/23040123750.00
5Cool Springs Mall, LLC5225.01.Q40.D.GParking Leases5/1/23050123750.00
6Cool Springs Mall, LLC5225.01.Q40.D.GParking Leases6/1/23060123750.00
7Crexie5090.01.Q40.D.GDues, Subs, Tuition (G&A)4/30/23Crexie250.00
8Crexie5090.01.Q40.D.GDues, Subs, Tuition (G&A)5/31/23Crexie125.00
9Crexie5090.01.Q40.D.GDues, Subs, Tuition (G&A)6/30/23Crexie125.00
10Indirect Expenditure9920.99.Q40.D.GIndirect Cost Recovered4/30/23Indirect Expenditure1,854.42
11Indirect Expenditure9920.99.Q40.D.GIndirect Cost Recovered5/31/23Indirect Expenditure2,599.99
12Indirect Expenditure9920.99.Q40.D.GIndirect Cost Recovered6/30/23Indirect Expenditure2,843.75
13Journal Communicatio5090.13.Q40.D.GPrint Advertising (G&A)4/30/23Journal Communicatio254.17
14Journal Communicatio5090.13.Q40.D.GPrint Advertising (G&A)5/31/23Journal Communicatio254.17
15Journal Communicatio5090.13.Q40.D.GPrint Advertising (G&A)6/30/23Journal Communicatio254.17
16Linked In5090.01.Q40.D.GDues, Subs, Tuition (G&A)4/30/23Linked In55.99
17Linked In5090.01.Q40.D.GDues, Subs, Tuition (G&A)5/31/23Linked In55.99
18Linked In5090.01.Q40.D.GDues, Subs, Tuition (G&A)6/30/23Linked In55.99
19New Wave Creative5020.02.Q40.D.GProfessional Services (G&A)4/30/23NewWave39.58
20New Wave Creative5020.02.Q40.D.GProfessional Services (G&A)5/31/23NewWave39.58
21New Wave Creative5020.02.Q40.D.GProfessional Services (G&A)6/30/23NewWave39.58
22On Your Mark Transportation5020.02.Q40.D.GProfessional Services (G&A)6/16/230616233,400.00
23On Your Mark Transportation5020.02.Q40.D.GProfessional Services (G&A)6/17/230617233,175.85
GL Q40


Q40 Invoice-Working.xlsm
ABCDEF
1Vendor NameInvoice No.TransactionTrans No.DateAmount
40Bankcard Center042423Invoice0424234/24/233,175.30
41Bankcard CenterPayment215545/10/23-3,175.30
42Bankcard Center052423Invoice0524235/24/236,240.45
43Bankcard CenterPayment08546/7/23-6,240.45
98Brand Wise2737Invoice27376/7/233,765.00
99Brand WisePayment08787/21/23-3,765.00
100Brand Wise2749Invoice27496/30/232,758.88
101Brand WisePayment08797/21/23-2,758.88
102Brandon Wilder04252Invoice042524/25/2317.00
103Brandon WilderPayment69554/26/23-17.00
Vendor Trans Hist-Macro



If you look Bank Card and Brand Wise both have matches but the cells are just coming back empty in column H were the formula is.
 
Upvote 0
I agree that Bankcard Center has a match
So we move on to the blue part below and E2 is not equal to VLOOKUP(A2,'Vendor Trans Hist-Macro'!A:D,4,0) as shown in the minisheet below where I have just put the blue part of the formula in H2 so it can easily be compared to E2. Since they are not equal the formula returns the FALSE (red) part of that IF statement

=IF(ISNUMBER(MATCH(A2, 'Vendor Trans Hist-Macro'!A:A, 0)), IF(E2 = VLOOKUP(A2, 'Vendor Trans Hist-Macro'!A:D, 4, 0), VLOOKUP(A2, 'Vendor Trans Hist-Macro'!A:D, 4, 0) + 1, ""), "")

pastorkc_1.xlsm
ABCDEFGH
1Vendor NameAccount IDAccount DescriptionDateReferenceDebit Amt
2Bankcard Center5090.01.Q40.D.GDues, Subs, Tuition (G&A)4507052423998.1642423
3Brand Wise5020.02.Q40.D.GProfessional Services (G&A)4508427373765
GL Q40
Cell Formulas
RangeFormula
H2H2=VLOOKUP(A2,'Vendor Trans Hist-Macro'!A:D,4,0)
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
update table
Replies
0
Views
275
BobtBuilder
B
P
  • Solved
Excel VBA, Vlookup to another sheet in same work book
Replies
6
Views
193
StephenCrump
S
S
  • Solved
Tweak Macro that Imports specific table columns from a different workbook to filter a column first
Replies
4
Views
176
sspatriots
S
A
  • Question
Code no longer working - Error: Variable not defined
Replies
1
Views
119
Rasm
R
Z
  • Question
Massive Nested IF Statement Help Needed
Replies
1
Views
131
Zutalores
Z

Forum statistics

Threads
1,215,159
Messages
6,123,348
Members
449,097
Latest member
thnirmitha

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