A better way to look?

[GAJITCS]

I have a table that is populated by other criteria.

EN4:EQ4 is defined as "EX_CA"


[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]EN[/TD]
[TD]EO[/TD]
[TD]EP[/TD]
[TD]EQ[/TD]
[TD]ER[/TD]
[TD]ES[/TD]
[TD]ET[/TD]
[TD]EU[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]PNL[/TD]
[TD]DP[/TD]
[TD]SP[/TD]
[TD]RM[/TD]
[TD]S[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]76[/TD]
[TD]17.18[/TD]
[TD]10.46[/TD]
[TD][/TD]
[TD]15.60[/TD]
[TD]14.47[/TD]
[TD]14.47[/TD]
[TD]S[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]77[/TD]
[TD]18.18[/TD]
[TD]19.43[/TD]
[TD]14.47[/TD]
[TD]15.60[/TD]
[TD]14.47[/TD]
[TD]14.47[/TD]
[TD]SP[/TD]
[TD]SP[/TD]
[/TR]
[TR]
[TD]78[/TD]
[TD]10.75[/TD]
[TD]9.3[/TD]
[TD]8.16[/TD]
[TD]12.53[/TD]
[TD]11.17[/TD]
[TD]11.17[/TD]
[TD]S[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]79[/TD]
[TD]8.09[/TD]
[TD]5.87[/TD]
[TD]4.88[/TD]
[TD]12.53[/TD]
[TD]11.17[/TD]
[TD]11.17[/TD]
[TD]S[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]80[/TD]
[TD]18.18[/TD]
[TD]14.28[/TD]
[TD]14.47[/TD]
[TD]15.60[/TD]
[TD]14.47[/TD]
[TD]14.47[/TD]
[TD]S[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]81[/TD]
[TD]18.18[/TD]
[TD]19.43[/TD]
[TD]14.47[/TD]
[TD]15.60[/TD]
[TD]14.47[/TD]
[TD]14.47[/TD]
[TD]S[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]82[/TD]
[TD]18.18[/TD]
[TD]14.43[/TD]
[TD]9.74[/TD]
[TD]15.60[/TD]
[TD]14.47[/TD]
[TD]14.47[/TD]
[TD]S[/TD]
[TD]S[/TD]
[/TR]
</tbody>[/TABLE]

Cell ES76 contains: {=IFERROR(SMALL(IF(EX_CA<>"X",IF(EN76:ER76<>0,EN76:ER76)),1),"")}
This picks out the smallest non zero value from EN76:ER76 where in the defined range EX_CA, there are no cells containing an "X". It's basically a lookup of the lowest value on the row, where the column is not excluded from the search. In this case, I have excluded columns EN to EQ by placing an X in each column on row 4. It works.

The values in EN76:ER82 are referenced from other cells in other sheets. Though there are cells on the same row reporting to be the same value as another cell on that row even when the cells are displayed to 10 decimal places, they are not being treated as being the same for some reason, but that is by the by. I'm not worried about that. The source data has been supplied to me and I just need to crunch this bit.

My issue is that in ETn, I need to identify from which column the value is ESn was found and return the corresponding cell value from row 5 of that column. We can see that ET77 picks up EP as being the column to return the value from.

ETn is an inelegant nested IF statement that asks =IF(ES77=EN77,EN5,IF(ES77=EO77,EO5... etc and isn't dealing with the minute difference that exists between the values of EP77 and ER77, hence the SP in ET77 rather than the S it should be. Remember that column EP is excluded from the formula in column ES.

If I wrap the formulas in EN76:ER82 with a ROUNDUP to 2 places, the problem is compounded and I actually end up with more values in ETn reporting the wrong values, so I am happy that the differences in the cells EN76:ER82 are what they should be without the ROUNDUP.

So... Onto the question.
Can I lookup the column that is actually in cells ESn from columns EN:ER where EX_CA is not X and return the cell value from row 5?

Many thanks.

 

[BrianJN1]

Honestly, while you've taken considerable effort with your post, I am thinking that with the number of views this has had, and the time since posting, that various persons have been overawed by the task.

By that I mean, you have given a rather truncated table of your Worksheet and then an explanation which is difficult to reconcile against the table given.

If there is nothing sensitive in it (otherwise remove/replace it) then I suggest that you post a copy of the full worksheet to a cloud sharing site (eg. Dropbox, GoogleDrive, your choice). Then there is no need for any of us to attempt a rebuild.

 

[GAJITCS]

Noted, Will simplify the question to the basic point and report.