one of two criteria

  • Thread starter Thread starter puiuluipui
  • Start date Start date
P

puiuluipui

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!
 
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!
 
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
 
If I understand, one way

=IF(OR(COUNTIF(Sheet2!$A$1:$C$3,A1),COUNTIF(Sheet2!$A$1:$C$3,B1)),c1,"")
 
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!
 
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!
 
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.
 
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
 
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
 
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),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 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
 
Back
Top