Single Formula to find fist column in a range with numeric value>0 for ONLY criteria row

Solved: 9/4/2013
I figured this formula out a few days ago before I posted, but had some errors in my data and did not realize it was correct.
If anyone sees an issue with it let me know... but I have tested it extensively on a very large array of 100 rows by 24 wide.

I attached a sample XLS with the solution!!!!


Thanks!


Original Post here:

I need a single array formula (one cell) to do this...

I have a worksheet where I need to find the MINimum COLUMN number from a range or rows/columns where:
#1) Only look at rows in the first range (A1:A4) where the first column "A" has a specific value (e.g., "HI"). Note that this may result in MULTIPLE rows applicable from the criteria Column "A".
#2) For rows matching from #1; find the first (leftmost) column in the range that has a numeric value > 0 in any cell (again, only for rows matching criteria).

I had a lot of permutations on this...only to realize that I am not getting there quickly. It must be a single formula and not VBA due to company policy.

general example
A B C D E F
1 xx 0 3 4 2 0
2 HI 0 8 0 0 0
3 yy 7 5 4 1 1
4 HI 0 0 1 0 1


So in the above example:
#1) My criteria is ALL rows having "HI" in column A. This is then rows #2 and #4 are to be inspected.
#2) ONLY for rows with "HI" (#2 and #4), look at values in all cells in range B1:F4 and give me the minimum column # where the value is > 0. For this example, with "HI" as the criteria, the formula should return column 3 ("C") since cell C2 is the first non-zero matching the >0 criteria for rows 2 & 4.


IMAGE of my current usage (but WITHOUt COL A criteria) is attached.
Screen Shot 2013-09-03 at 8.34.54 PM.png
Thanks!

vfrost

Hi and welcome to the forum

Please do not upload a picture of your file...rather, upload a sample of your workbook, showing what data you are working with, a few samples of your expected outcome is (manually entered is ok) and how you arrived at that. (exclude sensitive info). Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you Also, not all members can upload picture files (Company firewalls and stuff)

its pretty much the same process...
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.