# Help with lengthy nested IF statement

#### byrne31

##### New Member
This seems like a helpful place to get some advice on how to enter in a lengthy IF statement I am having trouble with. I am running Excel 2007 and have a spreadsheet I am using for a fantasy auto racing pool.

25 people are playing and they are to choose 5 drivers and I will enter them into this table. I am entering the drivers by car number so the numbers will vary from 1 to 99. Their finishing positions vary from 1 to 43 and will be added up with the lowest score winning for that week.

I need a formula that will automatically enter the data into the table based on the finishing positions. I have a column numbered 1 to 99 representing the car numbers and all I would need to do is enter the finishing position next to the respective car number and the table will populate automatically for everyone who had that number.

The IF statement I tried looks like this: I would go up to 99 but can't...

=IF(B3=1,Q1,IF(B3=2,Q2,IF(B3=3,Q3,IF(B3=4,Q4,IF(B3=5,Q5,IF(B3=6,Q6,IF(B3=7,Q7,IF(B3=8,Q8,IF(B3=9,Q9

When I get to 9 it reaches it's max number of nested IFs and I'm not sure which other functions I could use to make this work... Any help would be greatly appreciated. I will continue to research while this question is being pondered. Thanks.

Matt

### Excel Facts

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

you can use the Indirect function in this case.

=INDIRECT("Q"&B3)

Nowhere near as neat/clean for your case, but you can use defined names to get around the nesting limitation.

you can use the Indirect function in this case.

=INDIRECT("Q"&B3)

Thanks for this. When I entered the formula and gave it a try it returned "column 2". Then I realized I had headers on my table and I just deleted the headers and shifted the data up. Problem solved. It now Works! I appreciate it very much. This will save some data entry time for sure.

Thanks for this. When I entered the formula and gave it a try it returned "column 2". Then I realized I had headers on my table and I just deleted the headers and shifted the data up. Problem solved. It now Works! I appreciate it very much. This will save some data entry time for sure.

So with the headers, you need B3=1 return Q2, b3=3 return Q3 …

then =indirect("Q"&(b3+1))

The formula inside the call to indirect just builds a string that is the cell to reference.

So with the headers, you need B3=1 return Q2, b3=3 return Q3 …

then =indirect("Q"&(b3+1))

The formula inside the call to indirect just builds a string that is the cell to reference.

Thanks again!

Glad to help. I rarely use indirect but it does have uses.

Good luck with your project and again welcome to the forum.

Replies
3
Views
191
Replies
7
Views
120
Replies
5
Views
403
Replies
3
Views
182
Replies
0
Views
385

1,203,125
Messages
6,053,655
Members
444,676
Latest member
locapoca

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back