Hello All,
I need some help with a formula. I think it should be and INDEX function but I'm not sure. I want to look up every instance of the Sponsor in the table attached and show the Date Dropped and Quantity for the results. Haven't been able to make it work using VLookup or Index. Can you show me a formula that will work?

Attached Files

Index test.xlsx (21.6 KB, 12 views)

If I understand what you mean, gbaker, the correct answer is this: No matter what lookup function you use—VLOOKUP, MATCH, INDEX, whatever—the function is going to return a single value that can go only in that one cell. If you want multiple values, you have to use multiple formulae, one in each cell.

I've attached your workbook, modified to accomplish that. I didn't bother to use IF(ERROR...) functions so it looks messy, and someone else here may be able to show a better way of doing it generally. And of course you can always write a macro, though there may be reasons why that isn't a practical solution. This works...sort of.

Attached Files

x.xlsx (24.3 KB, 15 views)

Hi Bob,
Was able to make this work. I also tried to do it with another table. I wanted to have the date be the lookup. I moved the date column over to the 1st column and changed the formula but it didn't work. Should this also work if a date is being looked up.

Yeah, VLOOKUP and MATCH can use dates just as well as string values. Post a copy of the worksheet that doesn't work and I'll take a look at it.

Hi Bob,
I have attached a sample of what I am trying to do!!!

Attached Files

microsoft lookup date test.zip (80.7 KB, 10 views)

I see the problem. You're searching for a particular date in a column that has dates and times; there's no exact match.

Here's why: The timestamp "2014-01-15 22:39:23" is stored in Excel as 41654.9440162037. That's 41654 days from 1900-01-01 — which gets you to the end of 2014-01-14 — plus .9440162037 of another day, which gets you to 22:39:23 of the next day. Never mind that it looks like a year, month, day and time; the format code tells Excel to display it that way, but the internal value is one single large number.

So here you come along and ask the MATCH function to find 2014-01-15. But what Excel sees is 41654—that is, 41654.000—and when it tries to find 41654 in Master-date!B:B, it doesn't see anything that matches than value exactly.

So what do you do about it? Well, here's one way: I created a new column in in Master-date!I:I with formula =INT(B2). This takes the date from column B and throws away the fractional part; what's left is not a date and time, but just the date part. Then I changed the formula in 'Lookup by Date'!H:H to point to column I in Master-date instead of column B, like this:...Oops; I use R1C1 notation by habit. I mean it looks like this:I didn't check for any other errors, but the table all filled in, when I did that, with valid-looking data.

One thing: In column H of 'Lookup by Date', when I made that change, Excel displayed that column of row numbers as dates, eg "5-22". That means row 142, for the reason I explained above. The lookup and the rest of the table works fine, it just looks strange. All you have to do is changed the format of that column to General and it returns to normal.

@gbaker

Looking at your wbk I noticed that on the first sheet the date to be searched is placed in a merged cell.
Although it looks nice, if you have to work with it it could and will get you in trouble. So, except for purely cosmetic purposes, avoid using merged cells. Youcan replace then with the horizontal format " Center across selection" which does not have these drawbacks.

I'm also wondering why you do not simply use a filter on the date in you second sheet. Hit AltD+F+F and you can filter any day you want, or by sponsor, or whatever.

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Hi Bob,
That worked perfectly. Thanks for the Help!!!
Pecoflyer,
I took you advise and got rid of the merged cell. Both functions have value so I did as Bob suggested and also used the filter which is also a helpful tool for the team.

Thanks again!!!