Excel - COLUMN Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I need to check multiple cells in an array for a cell value and return value from row containing match ( cell from First column in that row)
Solved by E. H. in 25 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 04/08/2018 - 04:48
Hi…Welcome to Got It Pro
User 04/08/2018 - 04:48
hi
Excelchat Expert 04/08/2018 - 04:48
Could you please share a sample of your data?
User 04/08/2018 - 04:49
yes hold on
Excelchat Expert 04/08/2018 - 04:49
Thanks
User 04/08/2018 - 04:52
We need to check the array J5 to R20 and return true value from Row I5 to I20
Excelchat Expert 04/08/2018 - 04:54
Ok...so are you going to list out any value from J5 to R20 somewhere else where you need the column I values to populate?
Excelchat Expert 04/08/2018 - 04:57
Hi...are you there?
Excelchat Expert 04/08/2018 - 05:03
Please have a look at the formula in J26.
Excelchat Expert 04/08/2018 - 05:04
This is Index Match function.
User 04/08/2018 - 05:05
sorry got distracted somebody was talking
Excelchat Expert 04/08/2018 - 05:05
We are indexing column I for the matching row of the values that you have listed in the table array through Sumproduct and returning the values from column I>
Excelchat Expert 04/08/2018 - 05:06
-4 at the end that is used in the formula is just substracting the unused rows above the table. If you start the table in row 1, no need to use that.
User 04/08/2018 - 05:07
Nice one
Excelchat Expert 04/08/2018 - 05:07
Thanks
Excelchat Expert 04/08/2018 - 05:08
Is there anything else I can help you with this query?
User 04/08/2018 - 05:08
Is there a mothly fee or how does this work
Excelchat Expert 04/08/2018 - 05:08
Please check out for more info on payment options on our websites. We usually run promos and several subscription options.
User 04/08/2018 - 05:09
Do you have tutorials
Excelchat Expert 04/08/2018 - 05:10
We do not have tutorials at this moment. We only help resolve worksheet related issues, one query per session.
User 04/08/2018 - 05:10
Also can you email me the conversation please with the formula
Excelchat Expert 04/08/2018 - 05:11
We cannot email you the conversation. You can copy and save this is notepad for future reference.
Excelchat Expert 04/08/2018 - 05:11
For the file, you can download it directly by going to File >> Download as > MS Excel.
Excelchat Expert 04/08/2018 - 05:12
Formula used for your reference is
Excelchat Expert 04/08/2018 - 05:12
=INDEX($I$5:$I$20,SUMPRODUCT((I26=$J$5:$R$20)*(ROW($J$5:$R$20)-4)))
User 04/08/2018 - 05:12
Thanks a lot, i will check the subscription
Excelchat Expert 04/08/2018 - 05:13
Sure...please do subscribe.
Excelchat Expert 04/08/2018 - 05:13
Also please drop your valuable feedback at the exit of this session.
Excelchat Expert 04/08/2018 - 05:13
Thanks for your time. Please do come back for any new question. You can now end this session. Have a great weekend!
User 04/08/2018 - 05:13
ok Thanks Bye
User 04/08/2018 - 05:13
You too

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc