# if function - limit to the number of if/then inputs??? HELP!

#### polshek

##### New Member
i am an architect with an extremely large and unwieldy spreadsheet. the spreadsheet is set up with the row headings as a list of drawing numbers issued for my project and the column headings a list of all of the document issuances (with dates). each time there was a document issuance (such as asi #1 on 1.1.08), i marked an "X" in the cell that corresponded to each drawing that was released in asi #1. following this logic - asi #1 is column B (cell B1), drawing numbers column A (cell A2), first "X" would be B2. this set up allowed me to see (when reading vertically) all of the drawing numbers issued in a particular asi and it also lets me see (when reading horizontally) how many times each drawing was issued.

i want to establish a column that will tell me the last issue of each drawing such that, at a glance, i can see that drawing number 100 was last issued in asi #5 and drawing number 101 was last issued in asi #2.

i set up an if formula that worked, but only for 8 columns, then it said the formula was too large. the formula i set up was designed to check the rightmost column (last asi issue) and see if there was an "x" in the cell, if so it input the asi #, if not it checked the next cell to the left to see if there was an "x" in the cell, if so it input the asi #, if not it checked the next cell to the left and so on. - =IF(I2="X",\$I\$1,IF(H2="X",\$H\$1,IF(G2="X",\$G\$1,IF(F2="X",\$F\$1,IF(E2="X",\$E\$1,IF(D2="X",\$D\$1, IF(C2="X",\$C\$1)))))))

i know that excel is very powerful, and can almost read my mind at times...but i cannot get tha result i am after. am i doing something wrong in the if function? is there a better function to use? please help!!!

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

#### Jonmo1

##### MrExcel MVP
Welcome to the board...

In excel (prior to 2007) it is limited to 7 Nested Functions...

=INDEX(A1:I1,MATCH("X",A2:I2))

This will find the X furthest to the right, provided there are only X's or Blanks in the row..

Hope this helps..

#### Richard Schollar

##### MrExcel MVP
Hi

By the sounds of it the following formula should work:

=LOOKUP(2,1/(\$C2:\$I2="X"),\$C\$1:\$I\$1)

You can extend this to more columns as required.

#### schielrn

##### Well-known Member
Maybe try:

=lookup(2,1/(I2:C2="X"),I1:C1)

Hope that helps.

#### polshek

##### New Member
This worked! thank you!!!! can you explain what the function is actually doing? i have never used the lookup function. thanks again.

Replies
1
Views
152
Replies
1
Views
42
Replies
0
Views
48
Replies
17
Views
224
Replies
2
Views
96

1,191,705
Messages
5,988,185
Members
440,136
Latest member
dandanfielding

### 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