Array reference using indirect address

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to reference only the first cell of an array on a different worksheet
using a value in a cell on the current worksheet:

Current Worksheet
Cell B11 contains text from another function which is a cell reference i.e
AB13

I am trying to create a VLookup array on a different worksheet:
=VLOOKUP(8,Details!AB13:CT13,70)

I know I need to somehow replace the AB13 with &B11, but I cannot get it to
work correctly.

Thanks for the help....
 
Hmmm...

I just noticed that your table array is a single row:

Details!AB13:CT13

Is that a typo?
 
If B11 contains the starting cell of the array (AB13) you can use
=VLOOKUP(8,Details!INDIRECT(B11):CT13,70) and the formula becomes
=VLOOKUP(8,Details!AB13:CT13,70)
 
=VLOOKUP(8,Details!INDIRECT(B11):CT13,70)

Excel won't accept that as a formula. You have to put the entire table array
*inside* the INDIRECT function.

=VLOOKUP(8,INDIRECT("Details!"&B11&":CT13"),70)

But even this formula doesn't make sense since the table array is a single
row.
 
Excel 2007 accepts the formula =VLOOKUP(C1,INDIRECT(D1):B7,2). D1 contains
A1, the formula becomes =VLOOKUP(C1,$A$1,$B$7,2)
and returns the correct value from column B. If I use =SUM(INDIRECT(D1),A7)
the formula becomes SUM($A1$A7) and sums A1 thru A7
 
Correction. I always see my mistakes after I post. Sorry

Excel 2007 accepts the formula =VLOOKUP(C1,INDIRECT(D1):B7,2). D1 contains
A1, the formula becomes =VLOOKUP(C1,$A$1:$B$7,2)
and returns the correct value from column B. If I use =SUM(INDIRECT(D1):A7)
the formula becomes SUM($A1:$A7) and sums A1 thru A7
 
As I said I always see my errors after I post. I shall examine this post for
at least 1/2 hour before posting.

Correction. I always see my mistakes after I post. Sorry

Excel 2007 accepts the formula =VLOOKUP(C1,INDIRECT(D1):B7,2). D1 contains
A1. The formula becomes =VLOOKUP(C1,$A$1:$B$7,2)
and returns the correct value from column B. If I use =SUM(INDIRECT(D1):A7)
the formula becomes SUM($A$1:$A$7) and sums A1 thru A7
 
Excel 2007 accepts: =VLOOKUP(INDIRECT(A1),INDIRECT(B1):INDIRECT(C1),2)
Works fine. I don't have Excel 2003 to test this. I don't know why it
wouldn't. After all this is the whole purpose of indirection.
 
You are not doing the same formula as Biff, use the OP's original references
and lookup the values in another sheet. You are looking up values in the
same sheet

=VLOOKUP(8,INDIRECT(B11):INDIRECT(C11),70)

with AB13 in B11 and CT13 in C11 will work as long as you are looking up the
values in the same sheet, that is not the case. The OP clearly stated that
he is using VLOOKUP in another sheets or else it wouldn't make any sense
incorporating the sheet name.. That works in all Excel versions. The problem
is when you refer to another sheet, you cannot for instance put the sheet
name in D11 and use 3 INDIRECT you need to do like Biff . So what you are
saying is incorrect, it doesn't work like that in Excel 2007, it works
exactly like in previous versions where you have to build text string
referring to other worksheets


--

Regards,

Peo Sjoblom
 
Thank You....

I think I tried every possible iteration of quotes, except the one you
showed. The post was correct using a single row with Vlookup, maybe I could
have done it differently, but what I'm looking for is whether or not there is
an 8 in the first cell and if there is then I return the value from the last
cell of the array. The Vlookup is in an IF statement that returns null if
there is not an 8 in that first cell.

Everything in the worsheet is tied to a single cell that has a date, I can
change the date and all the references change, including the column # to
return at the end of the vlookup statement, which I showed as a constant in
the original post.

Mike
 
Except that it looks up the second but last cell, if you want the last cell
in AB13:CT13 you should change 70 to 71


--


Regards,


Peo Sjoblom
 
Back
Top