If isnumber lookup funciton not working

Hi,

I am attempting have excel look at two ranges. If it finds a value in A77 on the Final tab, in the range P2:P6 on the lists tab, then return the value in G77.

If it finds one of the values in the range P7:P8, then divide the value in G77 by two.
What am I missing?

=IF(ISNUMBER(LOOKUP(A77,lists!$P$2:$P$6)),G77,IF(ISNUMBER(LOOKUP(A77,lists!$P$7:$P$8)),(G77/2)))

Thank you

Hi Bryden2008- The LOOKUP function assumes a sorted list and finds the largest value less than or equal to. So it's finding 9398 (TRUE) instead of FALSE, which would go to your second range. Give me a sec, I'll find an alternative.

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

Thank you!

Try this:
=IF(ISTEXT(A77),0,IF(COUNTIF(lists!$P$2:$P$6,A77)>0,G77,IF(COUNTIF(lists!$P$7:$P$8,A77)>0,G77/2)))

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

Worked perfectly.
I forgot one request - if A77 is blank, how to not get false?

SOAKERS INV USAGE
SO2 3X10 RUBBER BACK OIL MAT 60 0

It is returning 0 for P7 "SO2" when it should be dividing by 2 yes?

Thank you!

That is perfect, thank you leenich!

Happy to help, thank you for the rep! If you're satisfied, please mark your thread as SOLVED (Thread Tools up top). Thanks-Lee