Multiple IF with VLOOKUP

Hi guys. I am creating a spreadsheet for weight loss. I am using the TODAY() function so excel knows to only look at todays figures when using VLOOKUP.

I am trying to create a formula that says different things depending on the persons BMI. For example "You are still overweight", or "Good work, keep it up!". The problem is, I can't use multiple IF statements. I keep getting a VALUE error.

I also want the formula to tell me to "weigh myself", if the BMI entry, and therefore the weight entry for the day, has not yet been inputted (ie the value = 0)

Here's what I have so far:

=IF(VLOOKUP(TODAY(),A2:D500,4,TRUE)>30,"You Are Still Obese!!","Getting there! Keep it up!!"),IF(VLOOKUP(TODAY(),A2:D500,4,TRUE)=0,"Weight yourself!")


So this above formula gives me a #VALUE error, but is saying "Look at today's BMI. If its over 30, it will say "you are still obese". If its under 30, then it will say "getting there, keep it up!"". I want a range of numbers such as between 25-30 saying "Good work, keep it up!", and 30+ saying "You are still obese", with a value of 0 giving a return of "Weigh yourself!", so essentially i need 3 different possible values (0, 25-30, and >30)

I hope this makes sense and really hope somebody can help me. I've been racking my brain all night!!

I attached a solution. Let me know if it doesn't work.

Try

=LOOKUP(VLOOKUP(TODAY(),A2:D500,4,TRUE),{0,25,31},{"Weigh yourself", "Good Work", "You are still obese"})

Thanks for this. It works to an extent. How does this formula work? I'd like to be able to understand it fully.

This seems to work. Thanks very much. Do the {} symbols mean that you used an array in this formula? Seems much more simple than other suggestions I've seen!