using hlookup to reference a column in a vlookup formula?

[joemeshuggah]

i am looking to have a specific spreadsheet reference a second sheet that is
variable daily in both rows and columns...a vlookup takes care of the rows,
but since the number of columns and/or column order may change, i cannot
hardcode the column index number in the vlookup formula.


i thought i could use a vlookup formula where the column referenced is based
on a column and hlookup function...i tried the following, but received an
error message:

=vlookup(a2,myrange,column(hlookup(b1,myrange,1,false)),false)

is there a way to accomplish this?

 

[bpeltzer]

Instead of the hlookup, I think you want match(b1,myrange,false).

 

[Peo Sjoblom]

=INDEX(MyRange,MATCH(A2,INDEX(MyRange,,1),0),MATCH(B1,INDEX(MyRange,1,),0))


a a1 a2 a3 a4
b 20 30 40 50
c 15 10 5 4
d 80 70 90 150

assume the above table called MyRange

you have c in A2 and a3 in B1 and you want to return
number 5 basically where the intersection of those 2 is

Of course if the table was D2:H5 you could use


=INDEX(D2:H5,MATCH(A2,D25,0),MATCH(B1,D2:H2,0))


if you have a named range like MyRange and use that in the formula you would
need the extra INDEX



--


Regards,


Peo Sjoblom

 

[joemeshuggah]

Thanks!!!!!

=INDEX(MyRange,MATCH(A2,INDEX(MyRange,,1),0),MATCH(B1,INDEX(MyRange,1,),0))


a a1 a2 a3 a4
b 20 30 40 50
c 15 10 5 4
d 80 70 90 150

assume the above table called MyRange

you have c in A2 and a3 in B1 and you want to return
number 5 basically where the intersection of those 2 is

Of course if the table was D2:H5 you could use


=INDEX(D2:H5,MATCH(A2,D25,0),MATCH(B1,D2:H2,0))


if you have a named range like MyRange and use that in the formula you would
need the extra INDEX



--


Regards,


Peo Sjoblom