A
Art
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!!!
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!!!