Match finds a matching value in a column or row

for a date =match(datetolookfor,columnofdates,0)

this gives you an offset into the columnofdates

you can use this in conjunction with other functions to get what you want

=index(B1:E1,match(1,offset(A1,match(Datetolookfor ,A2:A100,0),1,1,4),0))

This assume there is a 1 value in the row with that date.


for a specific column, it is even more straightforward to find the date

=index(A1:A100,Match(1,D1:D100,0),1)

--
Regards,
Tom Ogilvy



"SmilingPolitely" wrote in message
...
Nick Hodge wrote:
Yep,read that one wrong.... The issue will be trying to differentiate
between duplicate date values.

It might be handy for the OP to elaborate on what the end game is, as it
would seem that if the '1s' are to distinguish if they were sold that
day
for example, then why does it have dupe dates, when you could just enter
'1s' under several columns.

What I'm saying is, there may be a better data structure to get the
desired
result.

Thanks for the efforts, and thanks for finding the 'deliberate' error!
The last date in the test table is erroneous (sorry 'bout that!)


I did find a way to solve the problem using VBA, but the worksheet
method still has me perplexed.

The idea remains to find a column label, if there is a number (non-zero)
in a column for a particular date row.

I also need to reverse this and find say the date for a particular
product if there is a non-zero value in the column.

In another way,
in a given row with a non-zero intersection, what is the column, and
in a given column with a non-zero intersection, what is the row.

I think solving one will solve the other.

Also, there is only one non-zero value in each row, and only one
non-zero value in each column, for now!

[Hope that makes sense?]

Thanx.