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

S

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

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)
 
S

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
 
R

Ron Rosenfeld

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?
Click to expand...

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
 
Top