if vlookup question

[GCLIFTON]

I am trying to write

A2= 456758

J2= 450.75

If A2 AND J2 in sheet1 MATCH with in Detail tab A2 AND J2 THEN "" ,"Missing"

So A2 has a identifier that can repeat but j2 has the amount that relates to that identifier. So i am looking for 456758 and 450.75 in the detail tab. if it is there "" if false "Missing"

what is the proper way of writing this. Should it be vlookup should it be IF then statements should it be a if and statement.

 

[Scott T]

If you are only looking in A2 and J2 of each sheet then this will work.

=IF(AND(Sheet1!A2=detail!A2,Sheet1!J2=detail!J2),"","missing")

 

[GCLIFTON]

thank you i should have mention that [detail] has a ton of data, so it wouldn't be a cell for cell condition, but more of a search and find in a range of data for two met valued conditions ,where possible i was asking if i needed vlookup in this statement.

So i am looking for met condition if A2 and J2 together are found in the [detail] together, so if the

So here is what i currently have in real time:In A2 i have

=IF(C2>0,IF(Table_rpmwhsbidw_FormBuilder_UserDef_InvestorMatrix_1[JE Number]<>"",IF(ISERROR(VLOOKUP(Table_rpmwhsbidw_FormBuilder_UserDef_InvestorMatrix_1[JE Number],Detail!A:A,1,FALSE)),"Missing from detail",""),""),"")

And in B2 I have

=IF(A2="missing from detail",IF(ISERROR(VLOOKUP(Table_rpmwhsbidw_FormBuilder_UserDef_InvestorMatrix_1[[#Headers],[JE Number]],'2017CLEARED'!A:A,1,FALSE)),"not on cleared tab","cleared"),"")

i would like to rewrite this better for just one formula. Right now it is 2 in 2 different columns

 

[Scott T]

If I understand what you want then this should work.
This is an array formula and must be committed with CONTROL+SHIFT+ENTER. If done correctly Excel will put {} around the formula.

=IF(ISNUMBER(MATCH(Sheet1!A2&Sheet1!J2,detail!A2:A7&detail!J2:J7,0)),"","missing")

 

[GCLIFTON]

That didnt work, So i have updated my formula that i was using. Is there a way to combine the two. Right now i am filtering in both columns for missing in Detail and missing in 2017CLEARED. Is there a way to combine the two and have it return if not found then "Add to Detail Tab"

A2 HAS

=IF(C2="","",IFERROR(IF(VLOOKUP(Table_rpmwhsbidw_FormBuilder_UserDef_InvestorMatrix_1[@[JE Number]],Detail!A:A,1,FALSE)>0,"IN DETAIL TAB","Missing from Detail"),"Missing from Detail"))

B2 HAS

=IF(A2="in detail tab","",IF(C2="","",IFERROR(IF(VLOOKUP(Table_rpmwhsbidw_FormBuilder_UserDef_InvestorMatrix_1[@[JE Number]],'2017CLEARED'!A:A,1,FALSE)>0,"IN 2017CLEARED","Missing from CLEARED"),"Missing from CLEARED")))

 

[Scott T]

How did it now work?

Should the values you are looking for be on the same row in the detail tab or are they on different rows?