Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Functions
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Functions
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( A1:D1,MATCH(A10,A2:A5),0))) B10 shows 'Chips' "SmilingPolitely" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Functions
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 HIS "SmilingPolitely" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Functions
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. "Nick Hodge" wrote: 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 HIS "SmilingPolitely" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Functions
"Nick Hodge" wrote: 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 HIS "SmilingPolitely" wrote in message ... 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Functions
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Functions
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup / max & min functions | Excel Discussion (Misc queries) | |||
lookup functions | Excel Worksheet Functions | |||
Lookup functions | Excel Worksheet Functions | |||
LOOKUP functions? | Excel Worksheet Functions | |||
Lookup Functions | Excel Worksheet Functions |