"search" a different sheet with multiple criteria

I would like to know if its possible to use a formula like VLOOKUP using more
than one criteria in Excel 2007?

For example, say I have Sheet 1 that includes

A :: B :: C :: D
BUS 280 :: SU2009 :: online :: Art
BUS 340 :: FA2009 :: online :: Mike
MGT 240 :: FA2009 :: hybrid :: Steve
MKG 344 :: SP2010 :: online :: Terrance
MKG 401 :: SU2009 :: online :: Art

On Sheet 2, I want to have three drop down boxes in A1, B1, and C1, so the
user can select the criteria on which to search. (A1---Choose a course ID,
B1---Choose a first run date, C1---Choose a delivery method)

I want to use VLOOKUP (or some other formula) to display the results based
on one or more of the criteria selected using the three drop-down boxes. I
know how to display results over a group of rows using a single criteria, I
am just not sure if its possible to set up a formula that will look for, say
A1 & B1 & C1.

For a single search using a single criteria (and to eliminate the #N/A
problem) on a spreadsheet that deals with a similiar situation, i would use a
formula like this:

=VLOOKUP($B$5,INDEX(textbooks,SMALL(IF($B$5=INDEX( textbooks,,1),ROW(textbooks)-MIN(ROW(textbooks))+1,""),ROW(1:1)),,1),3,FALSE)))


If, say, they choose "SU2009" from the first drop-down box (A1), the
spreadsheet would show

BUS 280

If they choose FA2009 and "online" in drop downs A1 and B1, you would get

BUS 340
MGT 240

If they choose SU2009, "onlne", and "Art" in drop downs A1, B1, and C1, you
would get

BUS 280
MKG 344

(I will actually show more than just the course ID, but I can easily adapt a
formula for the adjacent cells if I know how to write the formula for the
first set of cells.)

Any ideas? Thanks!!!

You can add an extra column to your table in Sheet1 and concatenate
those three criteria together like this:

=A1&B1&C1

copied down. If this in column E then you can use an INDEX/MATCH
formula to match your 3 dropdowns with column E, as you now have a
single column to match on.

Hope this helps.

Pete

On Mar 10, 5:10*am, Art wrote:
I would like to know if its possible to use a formula like VLOOKUP using more
than one criteria in Excel 2007?

For example, say I have Sheet 1 that includes

A * * * * * * *:: B * * * * *:: C * * * *:: D
BUS 280 * *:: SU2009 :: online :: Art
BUS 340 * *:: FA2009 :: online :: Mike
MGT 240 * :: FA2009 :: hybrid *:: Steve
MKG 344 * :: SP2010 :: online *:: Terrance
MKG 401 * :: SU2009 :: online *:: Art

On Sheet 2, I want to have three drop down boxes in A1, B1, and C1, so the
user can select the criteria on which to search. (A1---Choose a course ID,
B1---Choose a first run date, C1---Choose a delivery method)

I want to use VLOOKUP (or some other formula) to display the results based
on one or more of the criteria selected using the three drop-down boxes. I
know how to display results over a group of rows using a single criteria, I
am just not sure if its possible to set up a formula that will look for, say
A1 & B1 & C1.

For a single search using a single criteria (and to eliminate the #N/A
problem) on a spreadsheet that deals with a similiar situation, i would use a
formula like this:

=VLOOKUP($B$5,INDEX(textbooks,SMALL(IF($B$5=INDEX( textbooks,,1),ROW(textboo*ks)-MIN(ROW(textbooks))+1,""),ROW(1:1)),,1),3,FALSE)))

If, say, they choose "SU2009" from the first drop-down box (A1), the
spreadsheet would show

BUS 280

If they choose FA2009 and "online" in drop downs A1 and B1, you would get

BUS 340
MGT 240

If they choose SU2009, "onlne", and "Art" in drop downs A1, B1, and C1, you
would get

BUS 280
MKG 344

(I will actually show more than just the course ID, but I can easily adapt a
formula for the adjacent cells if I know how to write the formula for the
first set of cells.)

Any ideas? Thanks!!!

Thanks!

Also found a great Web site with a great explanation. It was easy to adapt
the formula to include more than 2 criteria, too:

http://www.get-digital-help.com/2009...excel-formula/



"Pete_UK" wrote:

You can add an extra column to your table in Sheet1 and concatenate
those three criteria together like this:

=A1&B1&C1

copied down. If this in column E then you can use an INDEX/MATCH
formula to match your 3 dropdowns with column E, as you now have a
single column to match on.

Hope this helps.

Pete

On Mar 10, 5:10 am, Art wrote:
I would like to know if its possible to use a formula like VLOOKUP using more
than one criteria in Excel 2007?

For example, say I have Sheet 1 that includes

A :: B :: C :: D
BUS 280 :: SU2009 :: online :: Art
BUS 340 :: FA2009 :: online :: Mike
MGT 240 :: FA2009 :: hybrid :: Steve
MKG 344 :: SP2010 :: online :: Terrance
MKG 401 :: SU2009 :: online :: Art

On Sheet 2, I want to have three drop down boxes in A1, B1, and C1, so the
user can select the criteria on which to search. (A1---Choose a course ID,
B1---Choose a first run date, C1---Choose a delivery method)

I want to use VLOOKUP (or some other formula) to display the results based
on one or more of the criteria selected using the three drop-down boxes. I
know how to display results over a group of rows using a single criteria, I
am just not sure if its possible to set up a formula that will look for, say
A1 & B1 & C1.

For a single search using a single criteria (and to eliminate the #N/A
problem) on a spreadsheet that deals with a similiar situation, i would use a
formula like this:

=VLOOKUP($B$5,INDEX(textbooks,SMALL(IF($B$5=INDEX( textbooks,,1),ROW(textbooÂ*ks)-MIN(ROW(textbooks))+1,""),ROW(1:1)),,1),3,FALSE)))

If, say, they choose "SU2009" from the first drop-down box (A1), the
spreadsheet would show

BUS 280

If they choose FA2009 and "online" in drop downs A1 and B1, you would get

BUS 340
MGT 240

If they choose SU2009, "onlne", and "Art" in drop downs A1, B1, and C1, you
would get

BUS 280
MKG 344

(I will actually show more than just the course ID, but I can easily adapt a
formula for the adjacent cells if I know how to write the formula for the
first set of cells.)

Any ideas? Thanks!!!

.