having trouble with if functions

recordsheet.forum.xlsm


Hello, I am new but i have been racking my brain about this if function for a while. I am attempting to create a nested if function that returns a value from a database worksheet based on the name of a person entered.

=IF(ROWS(A$3:A4)>$F$1,"",INDEX(DataBase!$A$2:$A$1048576, SMALL(IF(DataBase!$B$2:$B$1048576=Athletname,ROW(DataBase!$B$2:$B$1048576)-1),ROWS(A$3:A4))))


this is the function that i am using.
so it states if the row count is greater than the number of data sets put nothing. otherwise index "database is the sheet name" if the database cell = the athlete name. type smallest value from the database.

now my problem is it works on the first line of my spreadsheet but when i copy it down i get #num error

but if i highlight the portions individually and hit F9 to calculate them out it will give me the right value.

I bolded all the worksheet names and name ranges.

my question was probably confusing so i posted a copy of my file hopefully that makes it easier.

You forgot to hit Ctrl + Shift + Enter.
This is how I would do it. Using Excel Table adapts the ranges so that formulas don't have to recalc 1 million, only the ranges that contain data.

The Ctrl + Shift + Enter converts the formula to an array formula. The brackets are an indicator that it has been converted to an array formula. Adding the bracket symbols alone have no effect.

Basically an array formula allows the functions within to accept a range as an argument instead of just a single cell.

Select one cell with a formula in, goto the Formulas banner and click Evaluate Formula (middle right). Do that with an array formula and you see there is a lot going on.

http://office.microsoft.com/en-us/ex...001087290.aspx
http://www.cpearson.com/excel/arrayformulas.aspx
http://spreadsheets.about.com/od/exc...s-tutorial.htm

I'll look at the formula tomorrow.

I didn't look at your file.

How many rows of data do you have?

You should avoid referencing the entire columns (less one row) in this portion of the formula:

=IF(ROWS(A$3:A4)>$F$1,"",INDEX(DataBase!$A$2:$A$1048576,SMALL(IF(DataBase!$B$2:$B$1048576=Athletname,ROW(DataBase!$B$2:$B$1048576)-1),ROWS(A$3:A4))))

Use smaller specific ranges.

Let's assume your data goes down to row 1000.

Try it like this...

Array entered**:

=IF(ROWS(A$3:A4)>$F$1,"",INDEX(DataBase!$A:$A,SMALL(IF(DataBase!$B$2:$B$1000=Athletname,ROW(DataBase!$B$2:$B$1000)),ROWS(A$3:A3))))


** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.