Formula needed please for a vlook up and match to return a specified value in a column

Hello everyone I have a problem I know what I need but can not seem to get it to work, I have 9 columns with 500+ rows of different possibilities from a drop down list. I need the items entered in the drop down list to be looked up in the table below with all the different possibilities, and return a specified value in column M

I am unsure how to do this, I have tried a vlook up and match but I think i am missing something as i cannot get it to work.

Any help would be great
The excel sheet is attached

Thank you for your help guys.

Hello jtakw
Thank you for the formula

But I have a question. When i open the file, and click on the cell with your formula there is a { at the beginning and a } at the end of the formula. As soon as I try to edit it the {} dissapears leaving the formula as =INDEX(N14:N521,MATCH(E5&F5&G5&H5&I5&J5&K5&L5&M5,E14:E521&F14:F521&G14:G521&H14:H521&I14:I521&J14:J521&K14:K521&L14:L521&M14:M521,0))

When I hit enter it comes back with a value error, when i try to put in the { and the} at the beginning and end of the formula it comes back as a text and does not calculate the value.

I need to edit the returned value from N14: s521??? or there abouts. what is going on? how come i can not edit the formula??

Please any advise would be great
And thank you.

Originally Posted by jtakw

Hi,

Good suggestion from mrice, otherwise you'll end up using a somewhat clumsy Array formula to be confirmed by CSE (Control, Shift, Enter):

Formula:

Please Login or Register  to view this content.

See attached.

Hello Mrice
I used your formula and got it to respond the 5 row without commas, but I can not seem to get it to work with referencing the table, I then took your formula applied it to all the 521 rows but still could not get a match
Any help would be great

Thank you

Originally Posted by mrice

Why not create a helper column in column D which contains a simple concatenation of the options in columns E to M.

e.g. =CONCATENATE(E5,F5,G5,H5,I5,J5,K5,L5,M5)

Once you have this, you can use a VLOOKUP function to return the value in column N

Read my post # 3, Formula Must be confirmed by CONTROL, SHIFT, ENTER, excel will automatically insert the curly brackets {} around the formula, do NOT try to type it in yourself.