CODEWITHSUNDEEP
excel-formula
Matt Ridge
Matt Ridge

Reputation: 3651

Lookup, with three if results

Ok, here is my issue... I get a #VALUE! as a result, but I can't figure out why... any help would be appreciated.

The first thing I am doing is referring to a ticket number which is in A2 on the same page, once found in the first lookup it then looks in Ticket_Import!G2 to see if the ticket is open or closed. If open then it says "Open" if closed it says "Closed", and if it's neither it is "Waiting". I can't figure why I am getting for all intended purposes a value error.

Any help would be appreciated. Below is the formula I'm using. 

=LOOKUP($A2,Ticket_Import!$B:$B,IF(Ticket_Import!$G2="closed","Closed",IF(Ticket_Import!G2="open","Open","Waiting")))

Upvotes: 1

Views: 83

Answers (1)

Edward
Edward

Reputation: 896

Try Index/Match vs Lookup

=if(index(Ticket_Import!$B:$G,Match($A2,Ticket_Import!$B:$B,0),6)="Open","Open",if(index(Ticket_Import!$B:$G,Match($A2,Ticket_Import!$B:$B,0),6)="Closed","Closed","Waiting"))

Upvotes: 1

Related Questions