I am trying to perform a lookup (vlookup) function in a cell in excel and wish to have the range as a variable, so that I can adjust which column the lookup function refers to.
Is this possible using the lookup function or do I have to code this in VB?Code:i.e. =lookup(a1,variable:$AB$100,1)
Jon
Last edited by jontw; 06-15-2009 at 05:06 AM.
=vookup(a1,B1:$AB$100,1,false)
it would be easier just to adjust the column number indicated above with a formula or just a cell ref.
oR use index /match
INDEX(B1:$AB$100,MATCH(A1,B1:B100,0),VARIABLE COLUMN NUMBER FORMULA)
exactly what do you have? give a small example
Mojito connoisseur and a dabbler in Cisco
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Okay,
What I have is three lookup tables which are 60coumns x60rows.
The first is pressure ratio against speed
The second is mass flow rate against speed
The third is efficiency against speed.
The speeds have equal intervals for example the first column will be 0rpm the second 1 krpm up to 60krpm.
For an input speed and pressure ratio I wish to find the efficiency and mass flow rate.
So I lookup the speed value I want using an hlookup and then find the next speed and establish the weighting of each speed i.e. using an input of 50.75 kpm will be 75% of 51krpm and 25% of 50krpm.
I then perform a vlookup using the pressure ratio for both speed (50 and 51krpm) from manually entering the column to find the row (might not be on the same row). To do this I set the row number in the 61st column and used aclumn index number of 61- column number. Having establish the rows and values of the pressure ratios of each speed I find the weighting of pressure ratio each speed column.
I then use the offset function for the next table to find the four cell values (i.e. offset of 50 and 51 and the rows) and use the pressure ratio weighting for each speed then the speed weighting to find a mass flow rate. This is then repeated for the efficiency table.
The major problem is I know the speeds rows but can’t seem to integrate this to the vlookup for the pressure ratios. I want this to be automatic so that it will hlookup the lower and higher speed then use this to vlookup the pressure ratios. The speed will change as this will then be iterated into another spreadsheet with test data inputs and can’t manually enter the range start column.
I hope this is clearer.
nope! a spread sheet with that stuff on would be better or i'd have to make one up myself.
indirect is also a possibility maybe?
=vookup(a1,INDIRECT("A"&COLUMN()&":$AB$100"),1,false)
Last edited by martindwilson; 06-12-2009 at 11:15 AM.
Mojito connoisseur and a dabbler in Cisco
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Thaniks for that,
It worked great, just me forgetting the indirect function and how to use it properly.
The code is now:
Where:Code:=VLOOKUP(K76,(INDIRECT(CELL("address",OFFSET(A226,0,K73-1)))):($BJ$286),1)
K73 is the speed column
A226 is the refence 0,0 for that table
K76 is the pessure ratio lookup value
BJ286 is tha maximum row and column cell for that table
Jon
Last edited by jontw; 06-15-2009 at 05:20 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks