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
Hello,

I need some help as I cant figure out how to construct this. I have been using a combination of IFS, AND, & VLOOKUP functions but I cant seem to figure out how to hit all the conditions i need.

I have specific values in 2 different columns that I need to compare against a list output a specific word against.

List A B
123 123 123
325 456 456
258 789 719
123 258
719 101
909


1. If the value in A is the same as the value in B text should be "local"
2. If the value in A is on the list and the value in B is not then text should be "remote"
3. If the value in B is on the list and the value in A is not then text should be "assigned"
4. if the value in A is blank but the value in B is on the list then text should be "available"
5. if the value in A is on the list but the value in B is not the text should be "vacant"
6. If the value in A and B are on the list but not the same values then the text should be "crossed"

Thanks in advanced. I have been trying to figure this out for 3 days.
Related:

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
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

0