I am working with a payroll spreadsheet. I would like to have vlookup choose
between two different tables to do a search. For example, for federal taxes
I have two tables, one for single and one for married. Instead of entering
"single" or "married" (the range names of my tables) in the actual formula, I
would like excel to lookup either or from a specific cell and act depending
of what is written on that cell (either "single" or "married"). The same
thing would go for the amount of allowances (which would be the column index
numbers.) I have tried writing the cell id, but excel gives me a "value"
error message. Would there be any possible solution out there? Thank you
very much.

Use an if statement to execute one fo two vlookups something like this

if(A1 = "Single", Vlookup(?, SingleRange, ?, false), if(A1 = "Married",
Vlookup(?, MarriedRange, ?, false), "Error"))
--
HTH...

Jim Thomlinson


"Roberto" wrote:

I am working with a payroll spreadsheet. I would like to have vlookup choose
between two different tables to do a search. For example, for federal taxes
I have two tables, one for single and one for married. Instead of entering
"single" or "married" (the range names of my tables) in the actual formula, I
would like excel to lookup either or from a specific cell and act depending
of what is written on that cell (either "single" or "married"). The same
thing would go for the amount of allowances (which would be the column index
numbers.) I have tried writing the cell id, but excel gives me a "value"
error message. Would there be any possible solution out there? Thank you
very much.

=VLOOKUP($A2,INDIRECT(E1),2,0)

E1="Single" or "Married"

"Roberto" wrote:

I am working with a payroll spreadsheet. I would like to have vlookup choose
between two different tables to do a search. For example, for federal taxes
I have two tables, one for single and one for married. Instead of entering
"single" or "married" (the range names of my tables) in the actual formula, I
would like excel to lookup either or from a specific cell and act depending
of what is written on that cell (either "single" or "married"). The same
thing would go for the amount of allowances (which would be the column index
numbers.) I have tried writing the cell id, but excel gives me a "value"
error message. Would there be any possible solution out there? Thank you
very much.

Fantastic. These work perfectly. Thank you both for your help.

Regards
Roberto

"Toppers" wrote:

=VLOOKUP($A2,INDIRECT(E1),2,0)

E1="Single" or "Married"

"Roberto" wrote:

I am working with a payroll spreadsheet. I would like to have vlookup choose
between two different tables to do a search. For example, for federal taxes
I have two tables, one for single and one for married. Instead of entering
"single" or "married" (the range names of my tables) in the actual formula, I
would like excel to lookup either or from a specific cell and act depending
of what is written on that cell (either "single" or "married"). The same
thing would go for the amount of allowances (which would be the column index
numbers.) I have tried writing the cell id, but excel gives me a "value"
error message. Would there be any possible solution out there? Thank you
very much.

Probably not an issue but it is important to note that Indirect is a volatile
function meaning that it is recacluated every time as opposed to only when
one of its precidents has changed. If you intend to have a lot (hundereds or
thousands) of these formulas then you will take a significant performance
hit. I personally avoid indirect (or any volatile function) wherever another
formula will work. Just my 2 cents...
--
HTH...

Jim Thomlinson


"rrodriguezdiaz30" wrote:

Fantastic. These work perfectly. Thank you both for your help.

Regards
Roberto

"Toppers" wrote:

=VLOOKUP($A2,INDIRECT(E1),2,0)

E1="Single" or "Married"

"Roberto" wrote:

I am working with a payroll spreadsheet. I would like to have vlookup choose
between two different tables to do a search. For example, for federal taxes
I have two tables, one for single and one for married. Instead of entering
"single" or "married" (the range names of my tables) in the actual formula, I
would like excel to lookup either or from a specific cell and act depending
of what is written on that cell (either "single" or "married"). The same
thing would go for the amount of allowances (which would be the column index
numbers.) I have tried writing the cell id, but excel gives me a "value"
error message. Would there be any possible solution out there? Thank you
very much.