Fetch for value and convert it to another according to condition

user_1128912 0
Viewed 20

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

vlookup google-sheets-formula match substitution regex
1 Answers

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

enter image description here

user_56326290
Related