Fetch for value and convert it to another according to condition-CodePudding

SHEET A - origin

Column A = ID
Column B = can contain "Accepted"( more text), "Rejected"( more text), "Partially Accepted"( more text)

SHEET B - destination

Column A = Column A from Sheet A
Column B = If Column B from Sheet A = "Accepted*" return 1, "Rejected*" return 2, "Partially*" return 3, else return 4

WHAT I've tried so far:

a) Works but I can't make it into an array

=IF(COUNTIF(SHEETA!A2,"*Rejected*"),2,IF(COUNTIF(SHEETA!A2,"*Partially*"),3,IF(COUNTIF(SHEETA!A2,"Accepted*"),1,4)))

b) Been trying to make it work (simplified version) but it's not working

=if((VLOOKUP(A2,A2:B2,2,FALSE))="Rejected*","2","1")

Can anyone give me a hand? Thank you in advance

CodePudding user response：

try in row 2:

=INDEX(IFNA(VLOOKUP(A2:A, {SheetA!A2:A, IFNA(CHOOSE(MATCH(
 REGEXEXTRACT(SheetA!B2:B, "(?i)accepted|rejected|partially accepted"), 
 {"accepted", "rejected", "partially accepted"}, ), 1, 2, 3), 4)}, 2, )))