| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
one of two criteria
Hi, i need a formula that retrieve data if one of two criteria is met. I need
the formula in "C". If A1 or B1=Sheet2!A1:D25,retrieve content of cell 3 Ex: Sheet 2: A B C John 32 AP Mary 28 TRP Jim 38 AP Sheet 1 A B C John =AP 28 =TRP Jim 38 =AP If one of two criteria is met, or if both criteria is met. Can this be done? Thanks! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
one of two criteria
Look in the help index for
OR -- Don Guillett Microsoft MVP Excel SalesAid Software "puiuluipui" wrote in message ... Hi, i need a formula that retrieve data if one of two criteria is met. I need the formula in "C". If A1 or B1=Sheet2!A1:D25,retrieve content of cell 3 Ex: Sheet 2: A B C John 32 AP Mary 28 TRP Jim 38 AP Sheet 1 A B C John =AP 28 =TRP Jim 38 =AP If one of two criteria is met, or if both criteria is met. Can this be done? Thanks! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
one of two criteria
Hi Don, i even searched on google, but i can make it wright.
=if(or(A1=Sheet2!A1:C3,B1=Sheet2!A1:C3,3,) This what i've got so far, but it really seems so far! Ofcourse it give's me an error! I don't know so much excel or english to completly understand what i have to do. Can you push me a little forward with this formula? Thanks allot! "Don Guillett" wrote: Look in the help index for OR -- Don Guillett Microsoft MVP Excel SalesAid Software "puiuluipui" wrote in message ... Hi, i need a formula that retrieve data if one of two criteria is met. I need the formula in "C". If A1 or B1=Sheet2!A1:D25,retrieve content of cell 3 Ex: Sheet 2: A B C John 32 AP Mary 28 TRP Jim 38 AP Sheet 1 A B C John =AP 28 =TRP Jim 38 =AP If one of two criteria is met, or if both criteria is met. Can this be done? Thanks! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
one of two criteria
try this formula
=IF(OR(A1=Sheet2!A1,B1=Sheet2!B1),Sheet2!C1,"") -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "puiuluipui" wrote: Hi Don, i even searched on google, but i can make it wright. =if(or(A1=Sheet2!A1:C3,B1=Sheet2!A1:C3,3,) This what i've got so far, but it really seems so far! Ofcourse it give's me an error! I don't know so much excel or english to completly understand what i have to do. Can you push me a little forward with this formula? Thanks allot! "Don Guillett" wrote: Look in the help index for OR -- Don Guillett Microsoft MVP Excel SalesAid Software "puiuluipui" wrote in message ... Hi, i need a formula that retrieve data if one of two criteria is met. I need the formula in "C". If A1 or B1=Sheet2!A1:D25,retrieve content of cell 3 Ex: Sheet 2: A B C John 32 AP Mary 28 TRP Jim 38 AP Sheet 1 A B C John =AP 28 =TRP Jim 38 =AP If one of two criteria is met, or if both criteria is met. Can this be done? Thanks! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
one of two criteria
Hi, it's working, but not from a range.
Can you make something like this? =VLOOKUP(A1, sheet1!A1:B251, 3, FALSE);(A1, sheet1!A1:B251, 2, FALSE) If A1 is empty, the code to look in B1. The code in A1 to retrieve data from cell 3 and if A1 is empty to look in B1 and to retrieve data from cell 2. Thanks in advance! "Francis" wrote: try this formula =IF(OR(A1=Sheet2!A1,B1=Sheet2!B1),Sheet2!C1,"") -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "puiuluipui" wrote: Hi Don, i even searched on google, but i can make it wright. =if(or(A1=Sheet2!A1:C3,B1=Sheet2!A1:C3,3,) This what i've got so far, but it really seems so far! Ofcourse it give's me an error! I don't know so much excel or english to completly understand what i have to do. Can you push me a little forward with this formula? Thanks allot! "Don Guillett" wrote: Look in the help index for OR -- Don Guillett Microsoft MVP Excel SalesAid Software "puiuluipui" wrote in message ... Hi, i need a formula that retrieve data if one of two criteria is met. I need the formula in "C". If A1 or B1=Sheet2!A1:D25,retrieve content of cell 3 Ex: Sheet 2: A B C John 32 AP Mary 28 TRP Jim 38 AP Sheet 1 A B C John =AP 28 =TRP Jim 38 =AP If one of two criteria is met, or if both criteria is met. Can this be done? Thanks! |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
one of two criteria
Hi Don, it's not working. it retrieve data but it doesn't matter if in A1 is
only a letter (k), or in B1 a only a number(9). Can you make something like this? =VLOOKUP(A1, sheet1!A1:B251, 3, FALSE);(A1, sheet1!A1:B251, 2, FALSE) If A1 is empty, the code to look in B1. The code in A1 to retrieve data from cell 3 and if A1 is empty to look in B1 and to retrieve data from cell 2. Thanks in advance! "Don Guillett" wrote: If I understand, one way =IF(OR(COUNTIF(Sheet2!$A$1:$C$3,A1),COUNTIF(Sheet2 !$A$1:$C$3,B1)),c1,"") -- Don Guillett Microsoft MVP Excel SalesAid Software "Francis" <xlsmate(AT)gmail(DOT)com wrote in message ... try this formula =IF(OR(A1=Sheet2!A1,B1=Sheet2!B1),Sheet2!C1,"") -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "puiuluipui" wrote: Hi Don, i even searched on google, but i can make it wright. =if(or(A1=Sheet2!A1:C3,B1=Sheet2!A1:C3,3,) This what i've got so far, but it really seems so far! Ofcourse it give's me an error! I don't know so much excel or english to completly understand what i have to do. Can you push me a little forward with this formula? Thanks allot! "Don Guillett" wrote: Look in the help index for OR -- Don Guillett Microsoft MVP Excel SalesAid Software "puiuluipui" wrote in message ... Hi, i need a formula that retrieve data if one of two criteria is met. I need the formula in "C". If A1 or B1=Sheet2!A1:D25,retrieve content of cell 3 Ex: Sheet 2: A B C John 32 AP Mary 28 TRP Jim 38 AP Sheet 1 A B C John =AP 28 =TRP Jim 38 =AP If one of two criteria is met, or if both criteria is met. Can this be done? Thanks! |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
one of two criteria
I'm still having a problem understanding what you want. Send your workbook to my address below along with an explanation and before/after examples. -- Don Guillett Microsoft MVP Excel SalesAid Software "puiuluipui" wrote in message ... Hi Don, it's not working. it retrieve data but it doesn't matter if in A1 is only a letter (k), or in B1 a only a number(9). Can you make something like this? =VLOOKUP(A1, sheet1!A1:B251, 3, FALSE);(A1, sheet1!A1:B251, 2, FALSE) If A1 is empty, the code to look in B1. The code in A1 to retrieve data from cell 3 and if A1 is empty to look in B1 and to retrieve data from cell 2. Thanks in advance! "Don Guillett" wrote: If I understand, one way =IF(OR(COUNTIF(Sheet2!$A$1:$C$3,A1),COUNTIF(Sheet2 !$A$1:$C$3,B1)),c1,"") -- Don Guillett Microsoft MVP Excel SalesAid Software "Francis" <xlsmate(AT)gmail(DOT)com wrote in message ... try this formula =IF(OR(A1=Sheet2!A1,B1=Sheet2!B1),Sheet2!C1,"") -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "puiuluipui" wrote: Hi Don, i even searched on google, but i can make it wright. =if(or(A1=Sheet2!A1:C3,B1=Sheet2!A1:C3,3,) This what i've got so far, but it really seems so far! Ofcourse it give's me an error! I don't know so much excel or english to completly understand what i have to do. Can you push me a little forward with this formula? Thanks allot! "Don Guillett" wrote: Look in the help index for OR -- Don Guillett Microsoft MVP Excel SalesAid Software "puiuluipui" wrote in message ... Hi, i need a formula that retrieve data if one of two criteria is met. I need the formula in "C". If A1 or B1=Sheet2!A1:D25,retrieve content of cell 3 Ex: Sheet 2: A B C John 32 AP Mary 28 TRP Jim 38 AP Sheet 1 A B C John =AP 28 =TRP Jim 38 =AP If one of two criteria is met, or if both criteria is met. Can this be done? Thanks! |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
one of two criteria
try this,
=IF(ISNA(IF(A2<"",VLOOKUP(A2,Sheet4!$A$2:$C$4,3,0 ),VLOOKUP(B2,Sheet4!$B$2:$C$4,2,0))),"not exist",IF(A2<"",VLOOKUP(A2,Sheet4!$A$2:$C$4,3,0), VLOOKUP(B2,Sheet4!$B$2:$C$4,2,0))) change the sheet and cells references to yours. I have added in for NA error handling, if data is not found, the formula will will return "not exist" Does this do what you want? -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "puiuluipui" wrote: Hi, it's working, but not from a range. Can you make something like this? =VLOOKUP(A1, sheet1!A1:B251, 3, FALSE);(A1, sheet1!A1:B251, 2, FALSE) If A1 is empty, the code to look in B1. The code in A1 to retrieve data from cell 3 and if A1 is empty to look in B1 and to retrieve data from cell 2. Thanks in advance! "Francis" wrote: try this formula =IF(OR(A1=Sheet2!A1,B1=Sheet2!B1),Sheet2!C1,"") -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "puiuluipui" wrote: Hi Don, i even searched on google, but i can make it wright. =if(or(A1=Sheet2!A1:C3,B1=Sheet2!A1:C3,3,) This what i've got so far, but it really seems so far! Ofcourse it give's me an error! I don't know so much excel or english to completly understand what i have to do. Can you push me a little forward with this formula? Thanks allot! "Don Guillett" wrote: Look in the help index for OR -- Don Guillett Microsoft MVP Excel SalesAid Software "puiuluipui" wrote in message ... Hi, i need a formula that retrieve data if one of two criteria is met. I need the formula in "C". If A1 or B1=Sheet2!A1:D25,retrieve content of cell 3 Ex: Sheet 2: A B C John 32 AP Mary 28 TRP Jim 38 AP Sheet 1 A B C John =AP 28 =TRP Jim 38 =AP If one of two criteria is met, or if both criteria is met. Can this be done? Thanks! |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
one of two criteria
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("L5").Address Then Range("H11") = Target Range("H10") = Sheets("Sheet2").Columns("B").Find(What:=Range("L5 "), _ LookIn:=xlValues, LookAt:=xlWhole, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Offset(, 3) End If If Target.Address = Range("M5").Address Then Range("H10") = Target Range("H11") = Sheets("Sheet2").Columns("e").Find(What:=Target, _ LookIn:=xlValues, LookAt:=xlWhole, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Offset(, -3) End If End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... I'm still having a problem understanding what you want. Send your workbook to my address below along with an explanation and before/after examples. -- Don Guillett Microsoft MVP Excel SalesAid Software "puiuluipui" wrote in message ... Hi Don, it's not working. it retrieve data but it doesn't matter if in A1 is only a letter (k), or in B1 a only a number(9). Can you make something like this? =VLOOKUP(A1, sheet1!A1:B251, 3, FALSE);(A1, sheet1!A1:B251, 2, FALSE) If A1 is empty, the code to look in B1. The code in A1 to retrieve data from cell 3 and if A1 is empty to look in B1 and to retrieve data from cell 2. Thanks in advance! "Don Guillett" wrote: If I understand, one way =IF(OR(COUNTIF(Sheet2!$A$1:$C$3,A1),COUNTIF(Sheet2 !$A$1:$C$3,B1)),c1,"") -- Don Guillett Microsoft MVP Excel SalesAid Software "Francis" <xlsmate(AT)gmail(DOT)com wrote in message ... try this formula =IF(OR(A1=Sheet2!A1,B1=Sheet2!B1),Sheet2!C1,"") -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "puiuluipui" wrote: Hi Don, i even searched on google, but i can make it wright. =if(or(A1=Sheet2!A1:C3,B1=Sheet2!A1:C3,3,) This what i've got so far, but it really seems so far! Ofcourse it give's me an error! I don't know so much excel or english to completly understand what i have to do. Can you push me a little forward with this formula? Thanks allot! "Don Guillett" wrote: Look in the help index for OR -- Don Guillett Microsoft MVP Excel SalesAid Software "puiuluipui" wrote in message ... Hi, i need a formula that retrieve data if one of two criteria is met. I need the formula in "C". If A1 or B1=Sheet2!A1:D25,retrieve content of cell 3 Ex: Sheet 2: A B C John 32 AP Mary 28 TRP Jim 38 AP Sheet 1 A B C John =AP 28 =TRP Jim 38 =AP If one of two criteria is met, or if both criteria is met. Can this be done? Thanks! |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
one of two criteria
Not sure if you get my response as I click on the post it shows blank...
it must ne the web hitch again. repost here try this, =IF(ISNA(IF(A2<"",VLOOKUP(A2,Sheet4!$A$2:$C$4,3,0 ),VLOOKUP(B2,Sheet4!$B$2:$C$4,2,0))),"not exist",IF(A2<"",VLOOKUP(A2,Sheet4!$2:$C$4,3,0),VL OOKUP(B2,Sheet4!$B$2:$C$4,2,0))) change the sheet and cells references to yours. I have added in for NA error handling, if data is not found, the formula will return "not exist" Does this do what you want? -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "puiuluipui" wrote: Hi, it's working, but not from a range. Can you make something like this? =VLOOKUP(A1, sheet1!A1:B251, 3, FALSE);(A1, sheet1!A1:B251, 2, FALSE) If A1 is empty, the code to look in B1. The code in A1 to retrieve data from cell 3 and if A1 is empty to look in B1 and to retrieve data from cell 2. Thanks in advance! "Francis" wrote: try this formula =IF(OR(A1=Sheet2!A1,B1=Sheet2!B1),Sheet2!C1,"") -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "puiuluipui" wrote: Hi Don, i even searched on google, but i can make it wright. =if(or(A1=Sheet2!A1:C3,B1=Sheet2!A1:C3,3,) This what i've got so far, but it really seems so far! Ofcourse it give's me an error! I don't know so much excel or english to completly understand what i have to do. Can you push me a little forward with this formula? Thanks allot! "Don Guillett" wrote: Look in the help index for OR -- Don Guillett Microsoft MVP Excel SalesAid Software "puiuluipui" wrote in message ... Hi, i need a formula that retrieve data if one of two criteria is met. I need the formula in "C". If A1 or B1=Sheet2!A1:D25,retrieve content of cell 3 Ex: Sheet 2: A B C John 32 AP Mary 28 TRP Jim 38 AP Sheet 1 A B C John =AP 28 =TRP Jim 38 =AP If one of two criteria is met, or if both criteria is met. Can this be done? Thanks! |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
one of two criteria
Hi, it's working! It's working very well!
Thanks! "Francis" wrote: Not sure if you get my response as I click on the post it shows blank... it must ne the web hitch again. repost here try this, =IF(ISNA(IF(A2<"",VLOOKUP(A2,Sheet4!$A$2:$C$4,3,0 ),VLOOKUP(B2,Sheet4!$B$2:$C$4,2,0))),"not exist",IF(A2<"",VLOOKUP(A2,Sheet4!$2:$C$4,3,0),VL OOKUP(B2,Sheet4!$B$2:$C$4,2,0))) change the sheet and cells references to yours. I have added in for NA error handling, if data is not found, the formula will return "not exist" Does this do what you want? -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "puiuluipui" wrote: Hi, it's working, but not from a range. Can you make something like this? =VLOOKUP(A1, sheet1!A1:B251, 3, FALSE);(A1, sheet1!A1:B251, 2, FALSE) If A1 is empty, the code to look in B1. The code in A1 to retrieve data from cell 3 and if A1 is empty to look in B1 and to retrieve data from cell 2. Thanks in advance! "Francis" wrote: try this formula =IF(OR(A1=Sheet2!A1,B1=Sheet2!B1),Sheet2!C1,"") -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "puiuluipui" wrote: Hi Don, i even searched on google, but i can make it wright. =if(or(A1=Sheet2!A1:C3,B1=Sheet2!A1:C3,3,) This what i've got so far, but it really seems so far! Ofcourse it give's me an error! I don't know so much excel or english to completly understand what i have to do. Can you push me a little forward with this formula? Thanks allot! "Don Guillett" wrote: Look in the help index for OR -- Don Guillett Microsoft MVP Excel SalesAid Software "puiuluipui" wrote in message ... Hi, i need a formula that retrieve data if one of two criteria is met. I need the formula in "C". If A1 or B1=Sheet2!A1:D25,retrieve content of cell 3 Ex: Sheet 2: A B C John 32 AP Mary 28 TRP Jim 38 AP Sheet 1 A B C John =AP 28 =TRP Jim 38 =AP If one of two criteria is met, or if both criteria is met. Can this be done? Thanks! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count how many criteria in a column match criteria in another colu | Excel Discussion (Misc queries) | |||
| EXCEL - Meet 2 criteria, then find next case of third criteria | Excel Worksheet Functions | |||
| =DMIN(database,field,criteria) question about criteria | Excel Discussion (Misc queries) | |||
| Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
| Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
Linear Mode
