one of two criteria

[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!A125,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!

 

[Don Guillett]

Look in the help index for
OR

 

[puiuluipui]

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!

 

[Francis]

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

 

[Don Guillett]

If I understand, one way

=IF(OR(COUNTIF(Sheet2!$A$1:$C$3,A1),COUNTIF(Sheet2!$A$1:$C$3,B1)),c1,"")

 

[puiuluipui]

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!

 

[puiuluipui]

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]

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.

 

[Francis]

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

 

[Don Guillett]

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

 

[Francis]

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

 

[puiuluipui]

Hi, it's working! It's working very well!
Thanks!