Thread: Formula that returns the contents of a cell based on where a value appears

I would like to create a formula or write code that can do the following on the attached spreadsheet:

If the value of cell A (excluding the text, only the number) is found anywhere in the cells B2:F7, then the corresponding C value for where the A value is found is displayed in the far right columns. For example since 28-132 (A1) is found in E1, B5, B6, and B7 then...
G1 would be BC-2.09
H1 would be BC-2.05
I1 would be BC-2.04
J1 would be BC-2.03
and since the value in A5 is not found anywhere in the other columns then G5 would say "NONE".

Any help is greatly appreciated, and let me know if I need to include any additional information or if I need to clarify. Thank!

Why would G1 be the value of C1? it doesn't fall within the search criteria?

Simon, G1 would be C1 given A1 (#-#) is contained within E1.

@shieldsp1 - are you open to achieving this with VBA ?
Though this is possible using functions it won't be trivial and a VBA approach would be simpler IMO.

My other question - thinking ahead - your values in C imply "# to #"
Is it then possible for the number in A to be within the span but not be present in the string itself ? If so, presumably that should be trapped also ?

Example of the above:

A1: 28-132
C1: 28-128 TO 28-133

implication being A1 is within C1 despite not being present in the string itself.

Is the above a possibility (sample implies max span of 2 and therefore # would always appear in the string) but this needs to be covered as it would add complexity (significant)

DO, the range to work with doesn't encompass row 1, i just wanted to know how, when and why he would want to include row 1