vlookup returns 0 and 1900 date in range of cells when only 1 has been filled in.

GodzFire

New Member
Joined
Apr 30, 2018
Messages
20
I have the following setup: Each of my cells does a vlookup for a matching record in either of 2 other source data tabs. If it finds a match, it puts them in the associated cells. When the Date Replaced cell is filled in with a date, an 'x' is entered into the associated S column, via a conditional formatting formula.
NZ1QLRw.jpg


Discovered problem: If someone enters just 1-3 of the 4 pieces of information in one of the 2 other source data tabs but leave the others blank, Excel will still fill the remaining ones left, but with either 0s for text and 1900 for date, which then triggers the conditional formatting and puts the 'x' in column S when it shouldn't. I'm not sure how to make it so the other ones don't trigger / stay blank until something is actually inputted into them.

Here are my forumulas. Also if there is a better formula to accomplish what I am trying to do, I am totally open.
O: =IFERROR(VLOOKUP(C144,FYInv!L:O,3,FALSE),VLOOKUP(C144,'FY18 Ref'!L:O,3,FALSE))
P: =IFERROR(VLOOKUP(C144,FYInv!L:O,1,FALSE),VLOOKUP(C144,'FY18 Ref'!L:O,1,FALSE))
Q: =IFERROR(VLOOKUP(C144,FYInv!L:O,2,FALSE),VLOOKUP(C144,'FY18 Ref'!L:O,2,FALSE))
R: =IFERROR(VLOOKUP(C144,FYInv!L:O,4,FALSE),VLOOKUP(C144,'FY18 Ref'!L:O,4,FALSE))
S: =IF(ISNUMBER(R144),"x","")
 


=IF(IFERROR(VLOOKUP(C144,FYInv!L:O,3,FALSE),iferror(VLOOKUP(C144,'FY18 Ref'!L:O,3,FALSE),""))=0,"",
IFERROR(VLOOKUP(C144,FYInv!L:O,3,FALSE),iferror(VLOOKUP(C144,'FY18 Ref'!L:O,3,FALSE),"")))
 
Upvote 0

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Forum statistics

Threads
1,216,178
Messages
6,129,326
Members
449,501
Latest member
Amriddin

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