How can I mark something as ‘yes’ or ‘no’ based on when two cells match as well as whether another cell is blank — in Numbers

Question

I’m using Numbers on an iPad (I don’t have a Mac).

I have a list of physical tools on one tab. On another tab I have a table of dates and times when the tools were checked out and checked in.

On the first tab (where the tools are listed), I’d like to have a column that says whether or not the tool is available based on whether or not it’s been checked out but not checked back in.

So I want to write a formula that looks for the name of the tool on the second tab, and when it finds it, checks to see whether any of the checkin cells are blank. If there’s a blank one, the formula should print ‘no’. If there aren’t any blank cells, the formula should print ‘yes’.

Is this possible? If so, can anyone suggest a formula please? It’s a bit too complex for me… (I have rudimentary coding skills.)

Answer 1

Xlookup may help.

With XLOOKUP, you can find values in columns and rows, or look for exact, approximate or partial matches.https://www.apple.com/au/numbers/features/

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

For example:

XLOOKUP(<cell>,Tool checkin::A,Checkin,"no",0,-1)

Then an if statement can turn it into a yes/no value:

IF(<cell>=0,"no","yes")