If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"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!!! |
#2
|
|||
|
|||
"search" a different sheet with multiple criteria
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!!! |
#3
|
|||
|
|||
"search" a different sheet with multiple criteria
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!!! . |
Thread Tools | |
Display Modes | |
|
|