Comp. value in 2 columns against a list and disp. text in 3r
Closed
Rschewe1
Posts
1
Registration date
Tuesday March 15, 2022
Status
Member
Last seen
March 15, 2022
-
Updated on Mar 15, 2022 at 09:21 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Apr 11, 2022 at 12:02 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Apr 11, 2022 at 12:02 PM
Related:
- Forum text
- Tentacle locker 2 - Download - Adult games
- Five nights in anime 2 - Download - Adult games
- Euro truck simulator 2 download free full version pc - Download - Simulation
- Display two columns in data validation list but return only one - Guide
- Feeding frenzy 2 download - Download - Arcade
1 response
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Updated on Apr 25, 2022 at 12:00 PM
Updated on Apr 25, 2022 at 12:00 PM
Hi Rschewe1,
You can give the following formula a try:
=IF(IF(B2=C2,"local","")<>"",IF(B2=C2,"local",""),IF(IF(AND(ISERROR(VLOOKUP(B2,$A$2:$A$7,1,0)),B2<>""),"",IF(AND(ISERROR(VLOOKUP(C2,$A$2:$A$7,1,0)),C2<>""),"remote",""))<>"",IF(AND(ISERROR(VLOOKUP(B2,$A$2:$A$7,1,0)),B2<>""),"",IF(AND(ISERROR(VLOOKUP(C2,$A$2:$A$7,1,0)),C2<>""),"remote","")),IF(IF(AND(ISERROR(VLOOKUP(C2,$A$2:$A$7,1,0)),C2<>""),"",IF(AND(ISERROR(VLOOKUP(B2,$A$2:$A$7,1,0)),B2<>""),"assigned",""))<>"",IF(AND(ISERROR(VLOOKUP(C2,$A$2:$A$7,1,0)),C2<>""),"",IF(AND(ISERROR(VLOOKUP(B2,$A$2:$A$7,1,0)),B2<>""),"assigned","")),IF(IF(AND(B2="",IF(ISERROR(VLOOKUP(C2,$A$2:$A$7,1,0)),"")<>""),"available","")<>"",IF(AND(B2="",IF(ISERROR(VLOOKUP(C2,$A$2:$A$7,1,0)),"")<>""),"available",""),IF(IF(AND(C2="",IF(ISERROR(VLOOKUP(B2,$A$2:$A$7,1,0)),"")<>""),"vacant","")<>"",IF(AND(C2="",IF(ISERROR(VLOOKUP(B2,$A$2:$A$7,1,0)),"")<>""),"vacant",""),IF(IF(AND(B2<>C2,IF(ISERROR(VLOOKUP(B2,$A$2:$A$7,1,0)),"")<>"",IF(ISERROR(VLOOKUP(C2,$A$2:$A$7,1,0)),"")<>""),"crossed","")<>"",IF(AND(B2<>C2,IF(ISERROR(VLOOKUP(B2,$A$2:$A$7,1,0)),"")<>"",IF(ISERROR(VLOOKUP(C2,$A$2:$A$7,1,0)),"")<>""),"crossed",""),"Criteria not met"))))))
This is the formula for row 2, where the List is located in Column A (rows 2:7), Value A in Column B and Value B in column C.
Best regards,
Trowa
You can give the following formula a try:
=IF(IF(B2=C2,"local","")<>"",IF(B2=C2,"local",""),IF(IF(AND(ISERROR(VLOOKUP(B2,$A$2:$A$7,1,0)),B2<>""),"",IF(AND(ISERROR(VLOOKUP(C2,$A$2:$A$7,1,0)),C2<>""),"remote",""))<>"",IF(AND(ISERROR(VLOOKUP(B2,$A$2:$A$7,1,0)),B2<>""),"",IF(AND(ISERROR(VLOOKUP(C2,$A$2:$A$7,1,0)),C2<>""),"remote","")),IF(IF(AND(ISERROR(VLOOKUP(C2,$A$2:$A$7,1,0)),C2<>""),"",IF(AND(ISERROR(VLOOKUP(B2,$A$2:$A$7,1,0)),B2<>""),"assigned",""))<>"",IF(AND(ISERROR(VLOOKUP(C2,$A$2:$A$7,1,0)),C2<>""),"",IF(AND(ISERROR(VLOOKUP(B2,$A$2:$A$7,1,0)),B2<>""),"assigned","")),IF(IF(AND(B2="",IF(ISERROR(VLOOKUP(C2,$A$2:$A$7,1,0)),"")<>""),"available","")<>"",IF(AND(B2="",IF(ISERROR(VLOOKUP(C2,$A$2:$A$7,1,0)),"")<>""),"available",""),IF(IF(AND(C2="",IF(ISERROR(VLOOKUP(B2,$A$2:$A$7,1,0)),"")<>""),"vacant","")<>"",IF(AND(C2="",IF(ISERROR(VLOOKUP(B2,$A$2:$A$7,1,0)),"")<>""),"vacant",""),IF(IF(AND(B2<>C2,IF(ISERROR(VLOOKUP(B2,$A$2:$A$7,1,0)),"")<>"",IF(ISERROR(VLOOKUP(C2,$A$2:$A$7,1,0)),"")<>""),"crossed","")<>"",IF(AND(B2<>C2,IF(ISERROR(VLOOKUP(B2,$A$2:$A$7,1,0)),"")<>"",IF(ISERROR(VLOOKUP(C2,$A$2:$A$7,1,0)),"")<>""),"crossed",""),"Criteria not met"))))))
This is the formula for row 2, where the List is located in Column A (rows 2:7), Value A in Column B and Value B in column C.
Best regards,
Trowa