How to lookup a value from a ramge of data. Eg Column C Row D?

[ST]

I want to look up a value from a range of columns and rows.
For example, Wat is the value ast Column C Row D?

What is the fundtion to use?

[Peo Sjoblom]

Row D?
You can use INDEX if you want the value where 2 coordinates meet

=INDEX(A2:D10,ROW(2:2),COLUMN(C:C))

will lookup what's in C3 (3 columns from left A included and 2 rows down
from row 2)

--
Regards,

Peo Sjoblom


"ST" <[email protected]> wrote in message
news:[email protected]...
>I want to look up a value from a range of columns and rows.
> For example, Wat is the value ast Column C Row D?
>
> What is the fundtion to use?

[ST]

Hi, Thanks for your reply but i still do not get it.
Let me explain again

Lets say I have the below table on one sheet and the formula on another
seperate sheet
A B C D E
0.5 1.30 5.40 6.5 7.5 10.50
1.0
1.5
2.0 21.25
2.5

Lets say the user enter the following data,for row: C and Col: 2.0, so what
formula do i use to call out the value of 21.25.

The formula needs extract out the value of whatever information the user
keys in.

Kindly advise.

Thanks a lot

"Peo Sjoblom" wrote:

> Row D?
> You can use INDEX if you want the value where 2 coordinates meet
>
> =INDEX(A2:D10,ROW(2:2),COLUMN(C:C))
>
> will lookup what's in C3 (3 columns from left A included and 2 rows down
> from row 2)
>
> --
> Regards,
>
> Peo Sjoblom
>
>
> "ST" <[email protected]> wrote in message
> news:[email protected]...
> >I want to look up a value from a range of columns and rows.
> > For example, Wat is the value ast Column C Row D?
> >
> > What is the fundtion to use?
>
>
>

[Peo Sjoblom]

You would use it with MATCH, examples here

http://www.contextures.com/xlFunctions03.html

--
Regards,

Peo Sjoblom


"ST" <[email protected]> wrote in message
news:[email protected]...
> Hi, Thanks for your reply but i still do not get it.
> Let me explain again
>
> Lets say I have the below table on one sheet and the formula on another
> seperate sheet
> A B C D E
> 0.5 1.30 5.40 6.5 7.5 10.50
> 1.0
> 1.5
> 2.0 21.25
> 2.5
>
> Lets say the user enter the following data,for row: C and Col: 2.0, so
> what
> formula do i use to call out the value of 21.25.
>
> The formula needs extract out the value of whatever information the user
> keys in.
>
> Kindly advise.
>
> Thanks a lot
>
> "Peo Sjoblom" wrote:
>
>> Row D?
>> You can use INDEX if you want the value where 2 coordinates meet
>>
>> =INDEX(A2:D10,ROW(2:2),COLUMN(C:C))
>>
>> will lookup what's in C3 (3 columns from left A included and 2 rows down
>> from row 2)
>>
>> --
>> Regards,
>>
>> Peo Sjoblom
>>
>>
>> "ST" <[email protected]> wrote in message
>> news:[email protected]...
>> >I want to look up a value from a range of columns and rows.
>> > For example, Wat is the value ast Column C Row D?
>> >
>> > What is the fundtion to use?
>>
>>
>>

[Ron Rosenfeld]

On Thu, 21 Apr 2005 20:59:01 -0700, "ST" <[email protected]> wrote:

>I want to look up a value from a range of columns and rows.
>For example, Wat is the value ast Column C Row D?
>
>What is the fundtion to use?

In Excel, using the A1 scheme, Columns are usually referenced by letters and
Rows by numbers.

But there are many different solutions depending on your specifics.

Depending on how your data is entered, you could use the intersection operator:

=Sheet1!2:2 Sheet1!C:C

would give you the value at cell C2.

If you are having the user enter the row in A1 (=2) and the column in B1 (=C)
(on a sheet other than your data), you could use the formula:

=INDIRECT("Sheet1!"&B1&A1)

If he is also entering the Sheet reference in, let us say, C1, and the column
NUMBER (instead of the column LETTER) then you could use the formula:

=INDIRECT(ADDRESS(A1,B1,,,C1))

You should be able to figure out something given this information.

Check out HELP for the intersection operator and the above mentioned functions.


--ron