The excel sheet comes over in horizontal rows what formula can be used to look up from horizontal to vertical?

Question

The excel sheet comes over in horizontal rows what formula can be used to look up from horizontal to vertical?

Shandreka Davenport 60

I need to change a text value into a numerical value to calculate the score. the information from the Form comes in horizontal :

User's image

I need to match it to this which is vertical to populate the score column

User's image

0 comments

Answer accepted by question author

0 additional answers

Your answer

Answer 1

You need to match the text response to the scoring table and return the number. If the response from the form is in J2, and your vertical guide has the answer text in column A and the numeric score in column B, use:

=XLOOKUP(TRIM(J2),$A$2:$A$6,$B$2:$B$6,"")

This compares the text in J2 to the vertical list of answers and returns the matching score.

If your version of Excel does not support XLOOKUP, use:

=VLOOKUP(TRIM(J2),$A$2:$B$6,2,FALSE)

TRIM() helps prevent matching problems caused by extra spaces from Forms responses. Place the formula in your Score column and drag it down for all rows.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin