Referring to cells by range names

R

rllane47

I used to know a formula years back that allowed me to return a specifi
value from a cell by referring to two ranges - one a row and the other
column. For example:

A B C
John 4 6 apple
Mary 5 GH 5
Pete 6 v hi

After naming the colums A, B and C and the rows John, Mary and Pete,
think it was using indirect to refer to A, John, returning 4; or B
Pete to return the value v. Anyone know what I am talking about an
could they give me that formula? Thanks
 
F

Frank Kabel

Hi
withtout naming them:
=INDEX(A1:C10,2,MATCH("John",A1:A10,0))

or
=VLOOKUP("John",A1:C10,2,0)
 
N

Niek Otten

The intersection operator is the space symbol. So "=A John" (space after the
A) gives you 4 etc. However, you can't use the name "C" which is reserved
for Column.


--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
Top