INDEX() columns first

[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?

[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 !
----------------------------------------------------------------------------
-------------------

"spxer" <[email protected]> wrote in
message news:[email protected]...

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?


--
spxer
------------------------------------------------------------------------
spxer's Profile:
http://www.excelforum.com/member.php...o&userid=37025
View this thread: http://www.excelforum.com/showthread...hreadid=568648

[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 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.

[spxer]

sorry the message box misaligned my excel example. I hope it still makes sence

[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._

[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



"spxer" <[email protected]> wrote in
message news:[email protected]...
>
> 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._
>
>
> --
> spxer
> ------------------------------------------------------------------------
> spxer's Profile:
> http://www.excelforum.com/member.php...o&userid=37025
> View this thread: http://www.excelforum.com/showthread...hreadid=568648
>

[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

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?

[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

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?

[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




"spxer" <[email protected]> wrote in
message news:[email protected]...
>
> __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
>
> 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?
>
>
> --
> spxer
> ------------------------------------------------------------------------
> spxer's Profile:
> http://www.excelforum.com/member.php...o&userid=37025
> View this thread: http://www.excelforum.com/showthread...hreadid=568648
>

[Peo Sjoblom]

Note that I assumed that your values are sorted in ascending order like in
your example


--


Regards,

Peo Sjoblom

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



"Peo Sjoblom" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
news:[email protected]...
> 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
>
>
>
>
> "spxer" <[email protected]> wrote in
> message news:[email protected]...
>>
>> __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
>>
>> 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?
>>
>>
>> --
>> spxer
>> ------------------------------------------------------------------------
>> spxer's Profile:
>> http://www.excelforum.com/member.php...o&userid=37025
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=568648
>>
>
>