Find result without use of macro
Posted by Paul on December 22, 2000 9:50 AM
I'm trying to find a non-macro based solution to the following but without much success.
I have a table of about 40 rows. After the 1st column, each row consists of formula derived consecutive numbers. A row can be any length. There are no column headers.
The left hand column consists of (non unique) narrative.
e.g 1st row: Wages 100, 101, 102
2nd row: Income 1300, 1301, 1302, 1303, 1304, 1305
3rd row: Wages 850, 851, 852, 853, etc.
Given a number, I want to find the narrative in the left hand column for the row in which that number appears. So, if the number was 1301 in the example above, I want to obtain the answer "Income" (i.e. I need to know the number appears somewhere in row 2).
Is there any way to do this via a combination of functions without writing a macro? It seems so simple in theory but Match will only handle single row ranges!
Any help most gratefully received.
Paul
Posted by Tim Francis-Wright on December 22, 2000 10:42 AM
Here's one way to do it.
Let's assume that the Wages/Income data is in
cells B1 through AZ40. A1 through A40 have the
category names.
If A42 has the input (1302),
then we have cell B42 as :-
IF(ISNA(MATCH($A$42,B1:B40,0)),0,MATCH($A$42,B1:B40,0))
and copy that formula over through AZ42.
Cell A43 has our answer:-
=OFFSET($A$1,MAX($B$42:$az$42)-1,0)
This is kind of ugly, and can probably be done
better as a user-defined function.
Good luck!
Posted by lenze on December 22, 2000 11:37 AM
One way would be to place the names to the right of your table. If your last colum is say R, in S1, put =A1, in S2 =A2, etc. Then use Lookup Array Form and include Column S in your array. You may hide column S if you wish to.
Posted by Dave on December 23, 2000 12:48 PM
Based on your example you could try:
=IF(COUNTIF(2:2,1301)<>0,INDEX(2:2,0,1),"")
Merry Xmas
OzGrid Business Applications
Posted by Paul Wakefield on January 04, 2001 12:41 AM
Thanks guys. Now back from Xmas break so can now give your suggestions a whirl. Apologies for delayed response.
Paul
