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.
