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

polshek

New Member
Joined
Jun 24, 2008
Messages
8
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!!!

thanks in advance for your time and your suggestions.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Welcome to the board...

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

But If i'm reading your formula right, this should work for you instead...

=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..
 
Upvote 0
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.
 
Upvote 0
This worked! thank you!!!! can you explain what the function is actually doing? i have never used the lookup function. thanks again.
 
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top