# 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))))

###### Who is Participating?

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.

Commented:
can you post sample where you have the data ?
gowflow
0
Commented:
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

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

Author 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. COMMENT.jpg
0
Author 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
Commented:
No, In Excel 2003 you'll need to keep specific ranges, rather than whole columns......

regards, barry
0
Author Commented:

=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
Commented:
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
Author 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
Commented:
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
Author 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
Commented:
Try like this

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

regards, barry
0
Author 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.