View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
CasaJay CasaJay is offline
external usenet poster
 
Posts: 1
Default Finding values in different rows/columns

This should be simple, but I'm lost. To greatly simplify my sheets:
1
11 12 13 14 15
21 22 23 24 25
31 32 33 34 35
41 42 43 44 45
2
11 12 13 14 15
21 22 23 24 25
31 32 33 34 35
41 42 43 44 45
I want to do a VLOOKUP(1,A1:A10,1,false) and return the value in D4, or
VLOOKUP(2,A1:A10,1,false) and return the value in C9. In other words, find my
A cell reference and return the value in a different row/column from that.

Another question: When I move a cell (cell1) to another cell (cell2) a cell
(cell3) that references cell1 moves its reference from cell1 to cell2. Is
there any way to anchor cell3's reference to cell1 without using the INDIRECT
function? INDIRECT does the job, but does not allow copying dynamically, i.e.
it retains the same cell's reference to the cell being copied to, maybe
because the quotations treat it as text?