Lookup Functions

[SmilingPolitely]

I have a spreadsheet that might be something like:

Apple Banana Chips Drink
01/05 0 0 0 1
02/05 0 1 0 0
04/05 1 0 0 0
04/05 0 0 1 0


What I need to do is read the value in the top row, given a particular
date (from the first column) to return the value from the top row where
the intersection of the row and column is 1

I cannot get my head around the lookup functions, lookup, hlookup,
vlookup etc to allow me to return the axis value given the intersection
value!

I hope someone can help, or at least point me in the right direction?

Thanks in advance.

 

[Patrick Molloy]

you have 4/5 in twice
assume the entire table is A1: E5

A10 has =A3
A10 shows 2-May

B10 has
=INDEX(A1:E1,,MATCH(1,OFFSET( A11,MATCH(A10,A2:A5),0)))

B10 shows 'Chips'

 

[Nick Hodge]

With your data in a table covering A2:E5 and with the two parameters in A8
(Date) and A9 (Type), you could use a combination of VLOOKUP and MATCH

=VLOOKUP($A$8,$A$2:$E$5,MATCH($A$9,$B$1:$E$1,0)+1,FALSE)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Guest]

Nick,
Me again! (thanks for your previous reply another posting).

I believe the need is too find the column which has a 1 in for a given row
(determined by date), and then determine the column header. Your solution
gives the value of the interesect of Date/Type which could be zero i.e.
assumes we know the Type.

Also in the test table, there are two dates the same so VLOOKUP chooses the
first: this maybe just a problem with data i.e. there cannot be two dates the
same.

Apologies if I misunderstand the problem and/or solution.

 

[Guest]

With your data in a table covering A2:E5 and with the two parameters in A8
(Date) and A9 (Type), you could use a combination of VLOOKUP and MATCH

=VLOOKUP($A$8,$A$2:$E$5,MATCH($A$9,$B$1:$E$1,0)+1,FALSE)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Nick Hodge]

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.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[SmilingPolitely]

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.

 

[Tom Ogilvy]

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,D1100,0),1)

--
Regards,
Tom Ogilvy

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.