• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 357
  • Last Modified:

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

0
holemania
Asked:
holemania
  • 6
  • 5
1 Solution
 
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
 
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now