Use Different table in Vlookup based on number input

[coyent]

I would like to use a different table to do a Vlookup based on a number I enter in a field. The number entered would be an interger between 1 and 8. The table to be used would be Data1, Data2 and so forth to Data8 corresponding with the number entered in the input field. The Vlookup to be changed would be

=IF(ISNUMBER(A7),VLOOKUP(A7,Data1,2,FALSE)," ")

If I enter "1" in the input field "Data1" would be unchanged
If I enter "2" in the input field "Data1" would change to Data2"

Any ideas on how to do this?

Don

 

[coyent]

The input field is NOT the one in the IF statement...it is on another worksheet within the workbook

 

[lenze]

You can use a combination of INDIRECT and INDEX. In the example, the yellow area is the data table named Data3. The listings in C2:C4 can be located on another sheet if need be. The formula checks the value in A2 and returns the corresponding label from C2:C4

HTH

Book1
	A	B	C	D	E	F
1	TableWanted				Field	NAME
2	3		Data1		5	BOB
3			Data2		6	JOE
4			Data3		7	BILL
5					8	SALLY
6	LookupValue
7	5		BOB
Sheet1

 

[just_jon]

I would like to use a different table to do a Vlookup based on a number I enter in a field. The number entered would be an interger between 1 and 8. The table to be used would be Data1, Data2 and so forth to Data8 corresponding with the number entered in the input field. The Vlookup to be changed would be

=IF(ISNUMBER(A7),VLOOKUP(A7,Data1,2,FALSE)," ")

If I enter "1" in the input field "Data1" would be unchanged
If I enter "2" in the input field "Data1" would change to Data2"

Any ideas on how to do this?

Don

Use the INDIRECT function -- if the 1/2 bit were in sheet2, cell c2 then

=IF(ISNUMBER(A7),VLOOKUP(A7,INDIRECT("data"&sheet2!C2),2,FALSE),"')

 

[Mark W.]

You can avoid the use of a volatile function such as INDIRECT by using...

=IF(ISNUMBER(A7),VLOOKUP(A7,CHOOSE(C2,Data1,Data2),2,FALSE)," ")