Excel - Vlookup Array with date

Hello expets,

I need help with an Array.  Not sure what the issue is, but I can't do an array if the data in the column is date or number only.

I have first sheet that pulls data from database.  Worksheet 2 is "Comment".  It looks at column A and B to do a lookup and then display data from column F if it match.  It works if I put text and numbers.  However, it will not work if my column F are dates.  Is there something wrong with the lookup that it's causing it to not work with date?

=LOOKUP("ZZZ", IF({1,0}, "", INDEX(COMMENT!F$1:F$9936, MATCH(1,(A2=COMMENT!A$1:A$9936)*(B2=COMMENT!B$1:B$9936),0))))

holemaniaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gowflowCommented:
can you post sample where you have the data ?
gowflow
0
barry houdiniCommented:
For numbers (or dates) you'd need that to be a little different, i.e.

=LOOKUP(99^99, IF({1,0}, 0, INDEX(COMMENT!F$1:F$9936, MATCH(1,(A2=COMMENT!A$1:A$9936)*(B2=COMMENT!B$1:B$9936),0))))

that will return 0 if there's no match

custom format result cell as m/d/yy;;

to return blank instead of date in 1900

for a version that works with text or dates (or numbers0

=IF(SUM((A2=COMMENT!A$1:A$9936)*(B2=COMMENT!B$1:B$9936)),INDEX(COMMENT!F$1:F$9936, MATCH(1,(A2=COMMENT!A$1:A$9936)*(B2=COMMENT!B$1:B$9936),0)),"")

regards, barry
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
holemaniaAuthor Commented:
Not sure if this will help, but attached are 2 screenshot.  I have the "master" worksheet that is data is pulled from database.  No one is to do anything with this one since data can change when refresh.  Then I have "comment" worksheet that allow the user to type in comments.  Worksheet "Master" has the lookup to "comment" and pull that into "Master".  The Scrap and Comments column work.  However, my Date column isn't carrying over. Worksheet 1COMMENT.jpg
0
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

holemaniaAuthor Commented:
barryhoudini,

That seems to work.  The 2nd one actually works well.

Is it possible to modify it so that I don't have to specify the cells to find?  

Example:
=IF(SUM((A=COMMENT!A:A)*(B=COMMENT!B:B)),INDEX(COMMENT!F:F, MATCH(1,(A=COMMENT!A:A)*(B=COMMENT!B:B),0)),"")

So look at the master list for all in column A and match with comment list.  Then do it for B from master list to comment list and display column F?  

Hoping not have to hard code which cell to search and range.  Would that work?  I tried it but it's showing nothing.

0
barry houdiniCommented:
No, In Excel 2003 you'll need to keep specific ranges, rather than whole columns......

regards, barry
0
holemaniaAuthor Commented:
Thanks.  Using your example:

=IF(SUM((A2=COMMENT!A$1:A$9936)*(B2=COMMENT!B$1:B$9936)),INDEX(COMMENT!F$1:F$9936, MATCH(1,(A2=COMMENT!A$1:A$9936)*(B2=COMMENT!B$1:B$9936),0)),"")



If my Master worksheet has nothing in Column A and B, the date is showing as 1/0/1900.  Is there any way to set it so that it's not doing that and make it blank instead?
0
barry houdiniCommented:
As per my comment in first answer, custom format like this

custom format result cell as m/d/yyyy;;

Note the two semi-colons at the end, if you include those then zeroes are shown as blanks

regards, barry
0
holemaniaAuthor Commented:
Awesome.  Thank you so much for your help.  Awarding you points.

Hopefully you can help answer a quick question in regard to the same lookup.  I'm doing a reverse lookup from "Comment" worksheet back to "Master" worksheet.  I am checking and see if Column A and B from "Comment" worksheet match "Master" worksheet.  If not, put in X in column G in "Comment" worksheet to indicate that the item doesn't exist.

Taking your example, again, would hard coding "X" it in there work?  It's showing up blank.

=IF(SUM((A2=MASTER!A$1:A$9936)*(B2=MASTER!B$1:B$9936)),INDEX("X", MATCH(1,(A2=MASTER!A$1:A$9936)*(B2=MASTER!B$1:B$9936),0)),"")
0
barry houdiniCommented:
Is that last formula simply required to see if there's a match?....or do you want to retrieve a specific value? What should the formula return if there is a matching row?

regards, barry
0
holemaniaAuthor Commented:
If it matches, to return blank.  However, if it doesn't match to mark it with an "X" in the Comment worksheet so user know that the item doesn't exists any more.

I can return data, but I just want to have it return an "X".  I tried putting "X" in there, but it's not doing anything.  It's pulling blank.
0
barry houdiniCommented:
Try like this

=IF(SUMPRODUCT((A2=MASTER!A$1:A$9936)*(B2=MASTER!B$1:B$9936)),"","X")

regards, barry
0
holemaniaAuthor Commented:
Thank you.  That worked great.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.