I have several worksheets containing similar (but not identical, as some
tables have more rows than others) tables of data. These worksheets are named
for different vehicle types (petrol, diesel, petrol SUV, diesel SUV etc.).
Each table contains information pertaining to different vehicle types, with
the first row listing various engine sizes (2000, 2500 etc.).
As part of a larger operation, I want users to select a vehicle-type from a
drop-down list on my main 'search' page and also enter their engine size. The
selected values will determine on which worksheet a lookup function performs
and for which row of that particular table the function will 'look'. The
function will then return the value in the relative column.

E.G.
A user selects 'petrol SUV' from the drop-down list and enters '2700' as
their vehicle's engine size. A vlookup function then looks at the table on
the worksheet 'petrol_SUV' and returns a value from the required column in
the same row as '3000' (next size up from the size the user entered).

I had tried using INDIRECT with VLOOKUP to get to the correct sheet but then
I'm stuck with accessing the correct row (which is determined by engine
size), from which to offset to the required column.

Another post provided part of the answer, which I thought I could combine,
but now I'm stuck again.Any help greatly appreciated.

Additionally, I'm unsure how to select the various possible tables (on each
worksheet) as they have the same structure, but differing numbers of rows
(petrol vehicles have more engine types than diesel vehicles).

Hope this makes sense and thanks in advance for any assistance.

You can use the vlookup / indirect functions to accomplish this.


try this formula -
=VLOOKUP(B3,INDIRECT(B2&"!$A:$C"),2,0)
assumes B2has the tab name you want to search under (i..e petrol,
diesel, etc.)
assumes B3 has the engine size

This also assumes that the various tabs are set up as follows:
Col A engine size (heading in row 1)
Col B Some attribute of engine i.e. fuel efficiency (heading in row 1)
Col C another engine attribute (heading in row 1)

=VLOOKUP($B$3,INDIRECT($B$2&"!$A:$c"),MATCH(C$6,IN DIRECT($B$2&"!
$1:$1"),0),0)

You could also use the formula above to dynamically tell the lookup
what column to return from the table

in the above example, it assumes that C6 has the name of the column
you want to retrieve from the various tabs.

On Jun 12, 6:50 am, Bill wrote:
I have several worksheets containing similar (but not identical, as some
tables have more rows than others) tables of data. These worksheets are named
for different vehicle types (petrol, diesel, petrol SUV, diesel SUV etc.).
Each table contains information pertaining to different vehicle types, with
the first row listing various engine sizes (2000, 2500 etc.).
As part of a larger operation, I want users to select a vehicle-type from a
drop-down list on my main 'search' page and also enter their engine size. The
selected values will determine on which worksheet a lookup function performs
and for which row of that particular table the function will 'look'. The
function will then return the value in the relative column.

E.G.
A user selects 'petrol SUV' from the drop-down list and enters '2700' as
their vehicle's engine size. A vlookup function then looks at the table on
the worksheet 'petrol_SUV' and returns a value from the required column in
the same row as '3000' (next size up from the size the user entered).

I had tried using INDIRECT with VLOOKUP to get to the correct sheet but then
I'm stuck with accessing the correct row (which is determined by engine
size), from which to offset to the required column.

Another post provided part of the answer, which I thought I could combine,
but now I'm stuck again.Any help greatly appreciated.

Additionally, I'm unsure how to select the various possible tables (on each
worksheet) as they have the same structure, but differing numbers of rows
(petrol vehicles have more engine types than diesel vehicles).

Hope this makes sense and thanks in advance for any assistance.

Tim, many thanks for your reply. This almost works as needed. However, I need
the formula to look at the next HIGHER engine size. At present it looks at
the next LOWEST, in the event there is no exact match. The reason is that
essentially my engine size column should read "up to 2000", "2001 - 2500",
"2501 - 3000" etc. but I've simplified this by only including the maximum
size.

In a previous post I asked only about this functionality and got a great
reply using INDEX and "+1" to increment the column number. Unfotunately I
have been unable to incorporate that into your formula.

Any further ideas?


"Tim879" wrote:

You can use the vlookup / indirect functions to accomplish this.


try this formula -
=VLOOKUP(B3,INDIRECT(B2&"!$A:$C"),2,0)
assumes B2has the tab name you want to search under (i..e petrol,
diesel, etc.)
assumes B3 has the engine size

This also assumes that the various tabs are set up as follows:
Col A engine size (heading in row 1)
Col B Some attribute of engine i.e. fuel efficiency (heading in row 1)
Col C another engine attribute (heading in row 1)

=VLOOKUP($B$3,INDIRECT($B$2&"!$A:$c"),MATCH(C$6,IN DIRECT($B$2&"!
$1:$1"),0),0)

You could also use the formula above to dynamically tell the lookup
what column to return from the table

in the above example, it assumes that C6 has the name of the column
you want to retrieve from the various tabs.

On Jun 12, 6:50 am, Bill wrote:
I have several worksheets containing similar (but not identical, as some
tables have more rows than others) tables of data. These worksheets are named
for different vehicle types (petrol, diesel, petrol SUV, diesel SUV etc.).
Each table contains information pertaining to different vehicle types, with
the first row listing various engine sizes (2000, 2500 etc.).
As part of a larger operation, I want users to select a vehicle-type from a
drop-down list on my main 'search' page and also enter their engine size. The
selected values will determine on which worksheet a lookup function performs
and for which row of that particular table the function will 'look'. The
function will then return the value in the relative column.

E.G.
A user selects 'petrol SUV' from the drop-down list and enters '2700' as
their vehicle's engine size. A vlookup function then looks at the table on
the worksheet 'petrol_SUV' and returns a value from the required column in
the same row as '3000' (next size up from the size the user entered).

I had tried using INDIRECT with VLOOKUP to get to the correct sheet but then
I'm stuck with accessing the correct row (which is determined by engine
size), from which to offset to the required column.

Another post provided part of the answer, which I thought I could combine,
but now I'm stuck again.Any help greatly appreciated.

Additionally, I'm unsure how to select the various possible tables (on each
worksheet) as they have the same structure, but differing numbers of rows
(petrol vehicles have more engine types than diesel vehicles).

Hope this makes sense and thanks in advance for any assistance.