INDEX() columns first

S

spxer

I want to use INDEX() or something similar to fiind a position of a
value. I need to reference the column first. INDEX() looks at rows
first. I really don't want to rearrange my table as it is 20 wide by
1000 long. Is there another solution?
 
R

RagDyeR

The Match() function returns positions.

Why not post some examples of what you're trying to accomplish?
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

message
I want to use INDEX() or something similar to fiind a position of a
value. I need to reference the column first. INDEX() looks at rows
first. I really don't want to rearrange my table as it is 20 wide by
1000 long. Is there another solution?
 
S

spxer

K L M N O P .............
1 100 200 300 400 500
2 1900 1900 1900 1900 1900
3 19240 19480 19720 19960 20200
4 19600 20200 20800 21400 22000

L1:AE1 is the table index headers
L2:AE1000 is the values to compare to
K will compare a value from H to L1:AE1 to determine the column an
then K will compare a value from I to the appropriate column to fin
the nearest number and return its row(2,3,4etc.)

I looked at Index(array,Match(),Match()), but Index looks for row firs
and I need to establish column first
 
S

spxer

This may be better


__K__ L____ M ____N ____O ____P .............
1___ 100___200_ _300 __400 __500
2____1900__1900 _1900 _1900 _1900
3____19240_19480 19720 19960 20200
4____19600_20200 20800 21400 22000

L1:AE1 is the table index headers
L2:AE1000 is the values to compare to
K will compare a value from H to L1:AE1 to determine the column and
then K will compare a value from I to the appropriate column to find
the nearest number and return its row(2,3,4etc.)

I looked at Index(array,Match(),Match()), but Index looks for row first
and I need to establish column first._
 
P

Peo Sjoblom

It all depends on how you use it, if for instance K2 tells which column to
look in then use it like

=INDEX(Table,Match(),K2)

Otherwise you need to come up with a better explanation with a concrete
example


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
 
S

spxer

__K____L____ _M ____N ____O ____P .............
1____100___200_ _300 __400 __500
2____19000_19000_19000_19000_19000
3____19240_19480_19720_19960_20200
4____19600_20200_20800_21400_22000
Click to expand...

I have a value in H3 of 251. I have a value in I3 of 19492.
The correct column to find is M. The correct row is 3. The result I
wish to return is 3. Suggestions?
 
S

spxer

__K____L____ _M ____N ____O ____P .............
1____100___200_ _300 __400 __500
2____19000_19000_19000_19000_19000
3____19240_19480_19720_19960_20200
4____19600_20200_20800_21400_22000
Click to expand...

I have a value in H3 of 251. I have a value in I3 of 19492.
The correct column to find is M. The correct row is 3. The result I
wish to return is 3. Suggestions?
 
P

Peo Sjoblom

This will return 3

=MATCH($I$3,INDEX($L$1:$P$4,,MATCH($H$3,$L$1:$P$1,1)),1)

if you want to return what's in the cell you need to build a bit more

=INDEX($L$1:$P$4,MATCH($I$3,INDEX($L$1:$P$4,,MATCH($H$3,$L$1:$P$1,1)),1),MATCH($H$3,$L$1:$P$1,1))

you can shorten it using offset however then the formula will be volatile




--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
 
Top