if statements with match

[jordanburch]

Hey guys,

I need help with this if statement. Basically i want it to say if this master key is found on this tab and another tab and a third tab then give me this text "EPS/ EPS Intransit". I will then make it reference two other tabs and give "EPS/ EPS Processed if it find the master key on both tabs. I then want it to just search for the master key on one tab and if its only on that one tab then insert the text "EPS" or "EPS Intransit" or "EPS Processed". Hopefully that makes sense. Ive got it sorta built out but cant seem to complete it.

here is the master key and it could go on any of the three tabs or just two tabs or just one tab. if its in three tabs i want it to say "EPS/EPS instransit/EPS Processed" and only two tabs i want it to be like the above statements and only one tab again like what i said above. Any ideas?

J

21-3875.001-4 - ALC 1700

 

[PankajMaheshwari]

@jordanburch Let's say you have 4 tabs (wiz. 1, 2, 3 & 4). If any item of tab 4 appears in tab 1, 2 & 3, you want "EPS intransit". If any item of tab 4 appears only in tab 1 & 2, you want "EPS processed". And if any item of tab 4 appears only in tab 1, you want "EPS". Is my understanding right?

If yes, your formula will be

=+IF(TYPE(XMATCH(C3,Sheet1!C:C)+XMATCH(C3,Sheet2!C:C)+XMATCH(C3,Sheet3!C:C))=1,"EPS/ EPS Intransit",IF(TYPE(XMATCH(C3,Sheet1!C:C)+XMATCH(C3,Sheet2!C:C))=1,"EPS Processed",IF(TYPE(XMATCH(C3,Sheet1!C:C))=1,"EPS")))

Hope this helps.

 

[jordanburch]

@jordanburch Let's say you have 4 tabs (wiz. 1, 2, 3 & 4). If any item of tab 4 appears in tab 1, 2 & 3, you want "EPS intransit". If any item of tab 4 appears only in tab 1 & 2, you want "EPS processed". And if any item of tab 4 appears only in tab 1, you want "EPS". Is my understanding right?

If yes, your formula will be

=+IF(TYPE(XMATCH(C3,Sheet1!C:C)+XMATCH(C3,Sheet2!C:C)+XMATCH(C3,Sheet3!C:C))=1,"EPS/ EPS Intransit",IF(TYPE(XMATCH(C3,Sheet1!C:C)+XMATCH(C3,Sheet2!C:C))=1,"EPS Processed",IF(TYPE(XMATCH(C3,Sheet1!C:C))=1,"EPS")))

Hope this helps.

hey bud i think this will work. I did find another way yesterday late. Here is the solution in case someone needs to use it.


=+IF(AND(ISNUMBER(MATCH(AW782,EPS!BS:BS,0)),ISNUMBER(MATCH('MAY FY20 EPS'!AW782,'EPS PROCESSED'!AB:AB,0)),ISNUMBER(MATCH('MAY FY20 EPS'!AW782,'EPS Intransit'!W:W,0))),"EPS/EPS Processed/EPS Intransit",IF(AND(ISNUMBER(MATCH(AW782,'EPS Intransit'!W:W,0)),ISNUMBER(MATCH('MAY FY20 EPS'!AW782,'EPS PROCESSED'!AB:AB,0))),"EPS Processed/EPS Intransit",IF(AND(ISNUMBER(MATCH(AW782,EPS!BS:BS,0)),ISNUMBER(MATCH('MAY FY20 EPS'!AW782,'EPS PROCESSED'!AB:AB,0))),"EPS/EPS Processed",IF(AND(ISNUMBER(MATCH('MAY FY20 EPS'!AW782,EPS!BS:BS,0)),ISNUMBER(MATCH('MAY FY20 EPS'!AW782,'EPS Intransit'!W:W,0))),"EPS/EPS Intransit",IF(ISNUMBER(MATCH(AW782,EPS!BS:BS,0)),"EPS",0)))))

 

[Eric W]

Here's another way:

=CHOOSE(ISNUMBER(MATCH(AW782,EPS!BS:BS,0))+ISNUMBER(MATCH(AW782,'EPS Processed'!AB:AB,0))*2+ISNUMBER(MATCH(AW782,'EPS Intransit'!W:W,0))*4+1,"On no sheet","EPS","EPS Processed","EPS/EPS Processed","EPS Intransit","EPS/EPS Intransit","EPS Processed/EPS Intransit","EPS/EPS Processed/EPS Intransit")

or even:

=SUBSTITUTE(SUBSTITUTE(IF(ISNUMBER(MATCH(AW782,EPS!BS:BS,0)),"EPS |","")&IF(ISNUMBER(MATCH(AW782,'EPS Processed'!AB:AB,0)),"| EPS Processed |","")&IF(ISNUMBER(MATCH(AW782,'EPS Intransit'!W:W,0)),"| EPS Intransit",""),"||","/"),"|","")

 

[jordanburch]

Here's another way:

=CHOOSE(ISNUMBER(MATCH(AW782,EPS!BS:BS,0))+ISNUMBER(MATCH(AW782,'EPS Processed'!AB:AB,0))*2+ISNUMBER(MATCH(AW782,'EPS Intransit'!W:W,0))*4+1,"On no sheet","EPS","EPS Processed","EPS/EPS Processed","EPS Intransit","EPS/EPS Intransit","EPS Processed/EPS Intransit","EPS/EPS Processed/EPS Intransit")

or even:

=SUBSTITUTE(SUBSTITUTE(IF(ISNUMBER(MATCH(AW782,EPS!BS:BS,0)),"EPS |","")&IF(ISNUMBER(MATCH(AW782,'EPS Processed'!AB:AB,0)),"| EPS Processed |","")&IF(ISNUMBER(MATCH(AW782,'EPS Intransit'!W:W,0)),"| EPS Intransit",""),"||","/"),"|","")

this is cool thanks. it works! I appreciate your help!

Jordan