Trying to use a vlookup

[laxster]

Simple issue here with vlookup but it's giving me a headache!

Anyhow, I have various columns showing the capacity of a bin for every item on every date. If a bin capacity drops to 0, I need to be able to return what date that occured on -- and there are 31 columns labeled with which day of the month it occured.

So if, say, wheat falls to 0 I would be able to see exactly which day this happened instead of having to look through a ton of other data.

I tried using a vlookup to search for 0s and return the day it happened, but I just can't make it do that!

Any ideas?

 

[laxster]

Any thoughts on using Pivot Tables with this? I'm getting frustrated, because it seems like this is a weird combination of vlookup and hlookup. Both formulas can look in the range I need, but neither formula does what I need it to do.

 

[chergh]

You want to use INDEX and MATCH

=INDEX(A1:F2,1,MATCH(0,A2:F2))

The above will return the value in row 1 of the column where it matches 0 in row 2 if you have data in the range A1:F2.

Not going to be the right solution if you have a bin reaching 0 more than once a month though.

 

[MI man]

One solution, though not effective, can be...
To use 3 fields to see the result.
Eg: if you want to see if wheat has fallen to 0, then
in one cell type in wheat, and here you can use VLOOKUP to see the value of wheat (in this case, 0) and then for the next cell (to view the date), you can either use INDEX and MATCH as told by Chergh, or try OFFSET.

Don't know if this works out effectively or not, for I have not tested it due to the lack of MS Office in the computer that am in now.
It it works, then it's well and good.

_____________________________________________________

Am no expert at anything....am a rookie at everything....

 

[NBVC]

Simple issue here with vlookup but it's giving me a headache!

Anyhow, I have various columns showing the capacity of a bin for every item on every date. If a bin capacity drops to 0, I need to be able to return what date that occured on -- and there are 31 columns labeled with which day of the month it occured.

So if, say, wheat falls to 0 I would be able to see exactly which day this happened instead of having to look through a ton of other data.

I tried using a vlookup to search for 0s and return the day it happened, but I just can't make it do that!

Any ideas?

What exactly is your setup?

Do you have multiple rows for each bin, or just one row?

Will there be a chance that more than one date needs to be returned if 0 occurs in multiple dates, etc...

Maybe upload a sample file detailing what you need.