Return a single text value from multiple columns

[Markmat]

Hello,
This is my first time on any forum, and I appreciate your assistance.

I receive the results of online surveys for the organisation I work for. These are received as Excel speadsheets. Recently the survey company changed the layout of the spreadsheet so that there is a column for each possible response to each field or question, but for which there will only be one answer in one of these columns. Please see the attached file - ResultCalc worksheet.

I need to return the correct answer for each question but I have not been successful using V/HLOOKUP, INDEX, etc. (I'm not trained or experienced with these.)

For example, in the attached file, in ResultCalc worksheet, columns O to AD are the columns which display the type of Asset Class. I'm trying to determine the correct formula to return the answer, which in this case is in column AC. I then want the answer to display in Job 1 worksheet in the Asset Class field (B12).

I then have several other questions and answers in the spreadsheet for which I'm trying to do the same.

Thanks very much for your help and I look forward to your suggestions.

 

[Nebu]

You Requirement is not clear you will have to explain a bit more on what you are looking for.

 

[Markmat]

You Requirement is not clear you will have to explain a bit more on what you are looking for.

I pressed the wrong button before I had finished writing. My apologies for the confusion. I have now completed my request. Thanks.

 

[NARAYANK991]

Hi ,

Your requirement is clear , but the problem is complicated because the information on the Job 1 sheet tab and the information on the ResultCalc sheet tab are not amenable to matching.

For some items on the Job 1 tab , the corresponding text on the ResultCalc tab is identical , for others it is not. For some items on the Job 1 tab , the corresponding text on the ResultCalc tab is in row 1 , while for others , it is in row 2.

If you can standardize on this , the formulae will be much simpler.

Narayan

 

[Markmat]

Hi ,

Your requirement is clear , but the problem is complicated because the information on the Job 1 sheet tab and the information on the ResultCalc sheet tab are not amenable to matching.

For some items on the Job 1 tab , the corresponding text on the ResultCalc tab is identical , for others it is not. For some items on the Job 1 tab , the corresponding text on the ResultCalc tab is in row 1 , while for others , it is in row 2.

If you can standardize on this , the formulae will be much simpler.

Narayan

Thanks for your comments. I'll see if I can change it.

 

[Haz]

To find the "Asset Class":

=INDEX(ResultCalc!O:AD,MATCH($B$3,ResultCalc!A:A,0),MATCH("*",INDIRECT("ResultCalc!O"&MATCH($B$3,ResultCalc!A:A,0)&":AD"&MATCH($B$3,ResultCalc!A:A,0)),0))

Huge formula, so let's go by parts.

MATCH($B$3,ResultCalc!A:A,0) looks for the ID and return the row number, in this case 4.

Result:
=INDEX(ResultCalc!O:AD,4,MATCH("*",INDIRECT("ResultCalc!O"&4&":AD"&4),0))

INDIRECT("ResultCalc!O"&,4&":AD"&,4) gives this array as a result: {0.0.0.0.0.0.0.0.0.0.0.0.0.0."Asset Class 15".0}

MATCH("*",{0.0.0.0.0.0.0.0.0.0.0.0.0.0."Asset Class 15".0},0) returns the position of first non-blank value on that array. In this case 15.

Result:
=INDEX(ResultCalc!O:AD,4,15)

Finally return the value in the 4th row, 15th column in ResultCalc!O:AD.
This returns "Asset Class 15".


To find the "Design Fit for Purpose" response, just change the values from O to AE and AD to AI:

=INDEX(ResultCalc!AE:AI,MATCH($B$3,ResultCalc!A:A,0),MATCH("*",INDIRECT("ResultCalc!AE"&MATCH($B$3,ResultCalc!A:A,0)&":AI"&MATCH($B$3,ResultCalc!A:A,0)),0))

The result is "Good".

 

[Markmat]

To find the "Asset Class":

=INDEX(ResultCalc!O:AD,MATCH($B$3,ResultCalc!A:A,0),MATCH("*",INDIRECT("ResultCalc!O"&MATCH($B$3,ResultCalc!A:A,0)&":AD"&MATCH($B$3,ResultCalc!A:A,0)),0))

Huge formula, so let's go by parts.

MATCH($B$3,ResultCalc!A:A,0) looks for the ID and return the row number, in this case 4.

Result:
=INDEX(ResultCalc!O:AD,4,MATCH("*",INDIRECT("ResultCalc!O"&4&":AD"&4),0))

INDIRECT("ResultCalc!O"&,4&":AD"&,4) gives this array as a result: {0.0.0.0.0.0.0.0.0.0.0.0.0.0."Asset Class 15".0}

MATCH("*",{0.0.0.0.0.0.0.0.0.0.0.0.0.0."Asset Class 15".0},0) returns the position of first non-blank value on that array. In this case 15.

Result:
=INDEX(ResultCalc!O:AD,4,15)

Finally return the value in the 4th row, 15th column in ResultCalc!O:AD.
This returns "Asset Class 15".


To find the "Design Fit for Purpose" response, just change the values from O to AE and AD to AI:

=INDEX(ResultCalc!AE:AI,MATCH($B$3,ResultCalc!A:A,0),MATCH("*",INDIRECT("ResultCalc!AE"&MATCH($B$3,ResultCalc!A:A,0)&":AI"&MATCH($B$3,ResultCalc!A:A,0)),0))

The result is "Good".

Thank you very much for this solution. I've put it into my spreadsheet and it works perfectly. Your help was much appreciated.