Can Vlookup accomplish this?

  • Thread starter Thread starter osp
  • Start date Start date
O

osp

Hey I was wondering if it is possible to return a row or column of cell
by changing the 1,1 at the very end of the function I tried plugging i
various numbers but it doesnt seem to work. I only get a single valu
from a single cell. I thought if I changed the +3,4,1,1) to
+3,4,10,1) it would return 10 cells but instead I get #VALUE! in th
cell
 
I'm not sure what formula you're using (you may want to post an example).

But if it's really =vlookup(), then those last two parameters return the column
in the lookup range to return and if it should look for an exact match (1=true
(not exact), 0=false (exact)).

Debra Dalgleish has nice instructions at:
http://www.contextures.com/xlFunctions02.html
 
Hi!

I replied to the original post a few weeks ago. IIRC, the
formula is an offset/match.

To answer the OP, no. The 1,1 are the HEIGHT and WIDTH
arguments in the OFFSET function. In your application the
cell values that you want returned are not contigious and
changing the 1,1 will result in errors as you've
experienced.

Biff
 
Hi

P.e. the formula
=OFFSET($A$1,3,4,10,1)
returns a range E4:E13, but such a formula in a cell has as much sinn as
=$E$4:$E$13
Try it and you also get #VALUE !

You can use OFFSET returning a range instead a single cell value only as a
range parameter in another function. P.e. the next formula is valid one:
=SUM(OFFSET($A$1,3,4,10,1))
 
Snip
.......

You can use OFFSET returning a range instead a single cell
value only as a range parameter in another function. P.e.
the next formula is valid one: =SUM(OFFSET($A$1,3,4,10,1))

.......

Well, that's the most common use but if you were to select
a 10 cell range and enter the formula in the formula bar
as an array:

=OFFSET(A1,3,4,10,1)

would return an array 10 rows high. However, this would
not work for the OP since the data they want returned
isn't in a contigious 10 cell range.

Biff
 
Back
Top