![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(28.799999999999997, 65%, 12%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESD%3C/text%3E%3C/svg%3E)
Microsoft 365 and Office | Excel | For business | Windows
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(291.2, 38%, 38%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EMP%3C/text%3E%3C/svg%3E)
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