Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
using hlookup to reference a column in a vlookup formula?
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,fa lse)),false) is there a way to accomplish this? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
using hlookup to reference a column in a vlookup formula?
Instead of the hlookup, I think you want match(b1,myrange,false).
"joemeshuggah" wrote: 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,fa lse)),false) is there a way to accomplish this? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
using hlookup to reference a column in a vlookup formula?
=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,D2:D5,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" wrote in message ... 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,fa lse)),false) is there a way to accomplish this? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
using hlookup to reference a column in a vlookup formula?
Thanks!!!!!
"Peo Sjoblom" wrote: =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,D2:D5,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" wrote in message ... 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,fa lse)),false) is there a way to accomplish this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Column reference in Vlookup formula | Excel Worksheet Functions | |||
Can you use HLOOKUP inside VLOOKUP to tell it what column 2 return | Excel Worksheet Functions | |||
vlookup and hlookup in the same formula | Excel Discussion (Misc queries) | |||
Vlookup/hlookup without using row or column numbers | Excel Worksheet Functions | |||
formula using both vlookup & hlookup | Excel Worksheet Functions |