need help using if, or, and vlookup, multiple errors

excelforumTracker2improved.xlsx

I'm trying to relearn functions, and I think I'm getting ahead of myself here, going beyond my abilities. this is what i have:

L3 =IF(E3="M",VLOOKUP(K3,'Male Tables'!$A$86:$I$147,IF(D3>61,11,ROUNDUP((D3-16)/5+1,0)),TRUE),VLOOKUP(K3,'Female Tables'!$A$59:$I$120,IF(D3>61,11,ROUNDUP((D3-16)/5+1,0)),TRUE))

This cell refers to 3 other cells for direction to find a score. E3 refers to Male or Female to decide which table array to search, using the initial IF. Obviously I've use "M" as true, thus it looks up the male score. if the cell does not contain "M" then it goes straight to the female table. D3 refers to a cell that contains age, the x factor in the table array if you will, and K3 is the number recorded during testing, the y factor in the table array and the result is the score. I adopted this from and old version of a calculator designed to score one individual at a time, male and female separately, and tweaked it to score a mass list of individuals. All of this works beautifully by the way, except when i try to make this return a blank cell if the recorded number, our M3 has not been entered. thus I have multiple rows of #N/A where scores are absent, and ##### in the resulting pass/fail cells that rely on these functions and would normally return a blank cell if only L3 were a blank cell.

Ill attach a brief workbook with the issues. It includes the original calculator I've based everything off of, which was made in 99 by the way, and up to date tables. Names have been changed for security reasons.

Row 7 is what it all looks like when everything is there. including conditional formatting on the expiration date.
Row 8 is the same except there is no start date and all the scores are fake, otherwise that would be #N/A like the rest. the issue there is, I'm trying to figure out how to get it to return blank/nohilight in h8 when g8 is blank, but instead it interprets blank as 0 and returns 180 days from the beginning of time, hence the red. formula used is thus:
=IFERROR(EDATE(G8,6),"")
Row 3 is last. this N3, similar to the function I started this off with, somehow interprets 0 or a blank cell as a perfect score. which normally would be true if Donny Dingo could run 2 miles in 0 seconds. but in this case 0 or blank means the event wasn't performed.

I know this is a lot, at least it seems so to me, ill appreciate help on any of these.

Anybody got any ideas? I'm beyond my current skill level here.

GizmoKip

Hi and welcome to the forum

A simple answer is to wrap your formula in iferror...
=iferror(IF(E3="M",VLOOKUP(K3,'Male Tables'!$A$86:$I$147,IF(D3>61,11,ROUNDUP((D3-16)/5+1,0)),TRUE),VLOOKUP(K3,'Female Tables'!$A$59:$I$120,IF(D3>61,11,ROUNDUP((D3-16)/5+1,0)),TRUE)),"")

That should do you for now, but I will take a look at your file and see if i can come up with anything else

OK, try the attached. I simplified your formulas by creating range names for each of your tables, and then referenced them using indirect() and the contents of cells in your 1st sheet

It actually is easy, all you need is to understand what indirect() does, and how to use it

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

If they are related to this specific 1, carry on here. If they are another question, start another thread

Thanks for the feedback