Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel formula Question
I'm trying to look at a range of cells based on two criteria, date and team.
If both the date and team show up I want the opponent to show up. Not sure how to use multiple vlookups for two criteria and if they both match bring back the appropriate response. In addition I want the value to be null if there is no match. Can anyone provide some thoughts. I've tried to use multiple vlookups and if then statements but its not working. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel formula Question
Try something like this:
=index(sheet2!$c$1:$c$100, match(1,(a2=sheet2!$a$1:$a$100)*(b2=sheet2!$b$1:$b $100),0)) (one cell) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can't use the whole column. This returns the value in Sheet2 column C when column A and B (of Sheet2) match A2 and B2 of the sheet (say Sheet1) with your values. So A2=Date, B2=Team to be matched while Sheet2 columns A,B and C are Date, Team and Opponent HTH "Lucas" wrote: I'm trying to look at a range of cells based on two criteria, date and team. If both the date and team show up I want the opponent to show up. Not sure how to use multiple vlookups for two criteria and if they both match bring back the appropriate response. In addition I want the value to be null if there is no match. Can anyone provide some thoughts. I've tried to use multiple vlookups and if then statements but its not working. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel formula Question
Hi!
Try this: G1 = lookup_value = some date H1 = lookup_value = some team A1:A20 = date range B1:B20 = team range C1:C20 = opponent range Formula entered as an array using the key combination of CTRL,SHIFT,ENTER: =IF(ISNA(MATCH(1,(A1:A20=G1)*(B1:B20=H1),0)),"",IN DEX(C1:C20,MATCH(1,(A1:A20=G1)*(B1:B20=H1),0))) Biff "Lucas" wrote in message ... I'm trying to look at a range of cells based on two criteria, date and team. If both the date and team show up I want the opponent to show up. Not sure how to use multiple vlookups for two criteria and if they both match bring back the appropriate response. In addition I want the value to be null if there is no match. Can anyone provide some thoughts. I've tried to use multiple vlookups and if then statements but its not working. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel formula Question
=IF(AND(TRUE, TRUE),
VLOOKUP(lookup_value,table_array,col_index_num)" ") My first shot at this was using the above statements. Inserting the VLOOKUP into the TRUE, TRUE parts of the IF(AND()). The trick is to get the VLOOKUP to return TRUE or FALSE depending on what it finds.... or something like that. I'd like to work on this but let me know if the following is and example of the table in which you are referencing. Team Date Opponent Lakers 06/07/06 Celtics Lakers 06/08/06 Suns Lakers 06/10/06 Suns Celtics 06/07/06 Lakers Pistons 06/07/06 76ers and so on... "Lucas" wrote: I'm trying to look at a range of cells based on two criteria, date and team. If both the date and team show up I want the opponent to show up. Not sure how to use multiple vlookups for two criteria and if they both match bring back the appropriate response. In addition I want the value to be null if there is no match. Can anyone provide some thoughts. I've tried to use multiple vlookups and if then statements but its not working. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
QUESTION: Copy pivottable from excel file to another. | Excel Discussion (Misc queries) | |||
excel division formula question | Excel Worksheet Functions | |||
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER | New Users to Excel | |||
Excel auto calculation formula question. | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions |