horizontal lookup

[pipemaker]

I have tried to do a horizontal lookup using multiple rows. This doesn't work. I can get it to work using a single row but not multiple. Any words of wisdom on how to get multiple rows to work?

 

[SamS]

pipemaker - can you post an example of your formula and table your are looking up

 

[pipemaker]

Heres a rough example

4/14 4/15 4/16 4/17 4/18 4/19

23 28 32 37 41 45
24 29 33 38 42 46
25 30 34 39 43 47
26 31 35 40 44
27 36



Something like the above. trying to do a horizontal lookup of the number and trying to get a return value of date. The array will not be sorted or anyhing like that. Not sure of the formula, just the basic hlookup wizard one.

 

[SamS]

On 2002-04-14 20:46, pipemaker wrote:
Heres a rough example

4/14 4/15 4/16 4/17 4/18 4/19

23 28 32 37 41 45
24 29 33 38 42 46
25 30 34 39 43 47
26 31 35 40 44
27 36



Something like the above. trying to do a horizontal lookup of the number and trying to get a return value of date. The array will not be sorted or anyhing like that. Not sure of the formula, just the basic hlookup wizard one.

hlookup doesn't work that way - the way your table is setup you would look up a date and search the array for a specific row number to return a value. Not sure of how to get around this one just now.

 

[nisht]

There are different ways for lookups..

hlookup, vlookup, offset, index - match,
lookup,, and you can ofcourse have your own lookup functions.

to your problem you can use offset or index-match function.

you can see how i have use some of this functions in my file no 24.

http://www.pexcel.com/download.htm

well i have not tried using dates . but it should work.

ni****h desai
http://www.pexcel.com

 

[Aladin Akyurek]

On 2002-04-14 20:46, pipemaker wrote:
Heres a rough example

4/14 4/15 4/16 4/17 4/18 4/19

23 28 32 37 41 45
24 29 33 38 42 46
25 30 34 39 43 47
26 31 35 40 44
27 36

Something like the above. trying to do a horizontal lookup of the number and trying to get a return value of date. The array will not be sorted or anyhing like that. Not sure of the formula, just the basic hlookup wizard one.

I'll assume that A1:F6 houses your example sample with A1:F1 holding the dates.

The problem statement. Given a number, retrieve the date that is in the same column as the number.

In H1 enter: 29 [ a target number ]

In I1 enter:

=IF(COUNTIF(A2:F6,H1)=1,INDEX(A1:F1,SUMPRODUCT((A2:F6=H1)*COLUMN(A2:F6))),"Either Absent Or Too Many")

As can be seen, this formula returns the text Either Absent Or Too Many if there are 0 or more than 1 occurrences of the target number in the relevant range.
This message was edited by Aladin Akyurek on 2002-04-14 23:41