Column Index Number Variations

[trainer75]

Hi,
I have this formula
=IF(ISNA(VLOOKUP($A10,INDIRECT($P1&"!A:j"),4,0)=TRUE),0,VLOOKUP($A10,INDIRECT($P1&"!A:j"),4,0))
but I would like to have the column index number variable, so that when I change a look up it will go to another column.
I have data in columns 4 through to 10 and would like to select it by column.

is this something that can be done?

Regards,

 

[jim may]

In a remote/empty cell, say Cell M1 enter your 4 variable (or any other column # between 4 and 10)

Change your Formula to:

=IF(ISNA(VLOOKUP($A10,INDIRECT($P1&"!A:j"),$M$1,0)=TRUE),0,VLOOKUP($A10,INDIRECT($P1&"!A:j"),$M$1,0))

 

[trainer75]

In a remote/empty cell, say Cell M1 enter your 4 variable (or any other column # between 4 and 10)

Change your Formula to:

=IF(ISNA(VLOOKUP($A10,INDIRECT($P1&"!A:j"),$M$1,0)=TRUE),0,VLOOKUP($A10,INDIRECT($P1&"!A:j"),$M$1,0))

hi there,

thank you for replying,
the 4 variables are columns I would like the look up to search for.
so for example column 4 is box, column 5 is crate, column 6 is trolley, I would like to choose them on a drop down box as there is info in all columns but I want it separate.
What would I enter into "M1" for it to be variable?

Regards,

 

[jim may]

Maybe something like this....

Excel 2012
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1							Enter Either Box, Crate or Trolley in K1 >>				Box		4
2
3													Box	4
4													Crate	5
5													Trolley	6
Sheet1

Cell Formulas
Range	Formula
M1	=VLOOKUP($K$1,$M$3:$N$5,2,0)

 

[bosco_yip]

hi there,

thank you for replying,
the 4 variables are columns I would like the look up to search for.
so for example column 4 is box, column 5 is crate, column 6 is trolley, I would like to choose them on a drop down box as there is info in all columns but I want it separate.
What would I enter into "M1" for it to be variable?

Regards,

Or this :

=IF(ISNA(VLOOKUP($A10,INDIRECT($P1&"!A:j"),MATCH($M$1,{"box";"crate";"trolley"},0)+3,0)=TRUE),0,VLOOKUP($A10,INDIRECT($P1&"!A:j"),MATCH($M$1,{"box";"crate";"trolley"},0)+3,0))

 

[trainer75]

Maybe something like this....

Excel 2012
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1							Enter Either Box, Crate or Trolley in K1 >>				Box		4
2
3													Box	4
4													Crate	5
5													Trolley	6
Sheet1

Cell Formulas
Range	Formula
M1	=VLOOKUP($K$1,$M$3:$N$5,2,0)

That worked brilliantly,
Thank you very much for your assistance, been wracking my brains for 2 days trying to find the link in the formula.
Much appreciated.

Kind Regards