Excel: if value is in column then return a different value

Use CHOOSE and AGGREGATE:

=CHOOSE(IFERROR(AGGREGATE(15,7,COLUMN($A$2:$B$5)/($A$2:$B$5=D2),1),2),"Limit","Ultimate","Neither")

The aggregate will return the column number 1 or 2 to the Choose which will then return the correct value depending on what the AGGREGATE returned.

Another is to use two MATCH in nested IFS:

=IF(ISNUMBER(MATCH(D2,A:A,0)),"Limit",IF(ISNUMBER(MATCH(D2,B:B,0)),"Ultimate","Neither))

With two columns the IF will be better as it will be quicker. If one gets more then the first will be easier to maintain.