arunav ghosh
asked on
Conditional Vlookup to take certain range of data only
Hello,
I'm new in excel and vlookup, so consider me a near zero knowledge in vlookup and conditional formatting.
query:
Advisor: Total CSAT%
Abhishek Deb 77.92%
AJAY KUMAR TIWARY 70.27%
DHIRAJ MITRA 88.89%
MANAS SAHA 65.85%
OLYMPIA LAHIRI 84.62%
Rumki Sanyal 72.00%
Aamir Faiz 78.13%
Aanchal Singh 85.04%
Abhishek Saha 70.10%
Abhishek Sen 85.48%
Afifa Shohab 67.74%
Ajit Kumar Sha 59.74%
Amarjeet Kour 77.05%
Amit Kumar Moulay 68.49%
Anand Kumar Gupta 70.24%
Anita Rana 86.90%
Ankita Laha 77.78%
Anuj Bhatia 82.50%
Argha Das 82.81%
Arinjay Mukherjee 94.92%
Aritra Chatterjee 82.35%
Arjun Jaiswal 81.99%
ARUNAV GHOSH 78.75%
Asish Karmakar 86.44%
Atandra Ghosh 68.29%
Avinash Barui 87.41%
Ayushman Sen 65.00%
Barkha Srivastava 52.73%
Beepasha Chettri 63.64%
Binay Das 79.45%
Bornali Guha Sarkar 76.84%
Chiranjit Roy 76.60%
Churni Chakraborty 63.27%
Debangan Kar 62.26%
Debjani Maitra 76.32%
Debjoy Das 86.67%
Dipika Pandey 84.93%
Eleseba Gurung 90.00%
Eshita Muunshi 86.46%
Hena Akram 90.63%
Himanshu Gupta 91.80%
Ishannila Biswas 86.05%
Jagriti Banik 78.95%
Kaushik Chatterjee 71.83%
Khushboo Patro 77.59%
Maitri Majumder 89.66%
Manash Pal 50.00%
Md Humayal Rashid 82.68%
Megha Singh 84.21%
Mou Mitra 73.17%
Nabanita Ghorai 75.68%
Nainy Roy Ghosh 79.41%
Neha Kumari 71.21%
Oindrilla Basu 70.24%
Parijat Sarkar 77.66%
Partho Jyoti Saikia 92.68%
Priyanka Kumari 76.79%
Priyanka Roy 67.39%
Puja Rakshit 61.54%
Purba Das 75.56%
Rahul kumar 82.73%
Raja Bera 90.91%
Rajbir Kaur Lakhotra 80.77%
RATUL DAS 81.58%
Reema Banerjee 66.67%
Rinzee Tshering Bhutia 77.46%
Riya Bhattacharya 85.29%
Rohan Behara 62.96%
Rohit Goyal 89.52%
Rubab Waheed 70.42%
S K Jangdai 76.92%
Sajal Roy 75.79%
Sandip Kumar Bhunia 51.61%
Sangeeta Rout 80.77%
Sangita Baraik 82.46%
Santanu Debnath 84.81%
Satwika Roy 90.24%
Sawdhin Kr Sarangi 78.38%
Sayontee Bagchi 74.51%
Sharmila Lama 87.12%
Shreshtha Sharma 75.47%
Shubhankar Basu 72.37%
Sneha Bhattacharyya 75.00%
Sohini Majumder 86.21%
Somali Chakraborty 84.62%
Sonia Sengupta 81.48%
Soumen Nandy 81.56%
Soumi Chakraborti 76.00%
Soumyajit Dasgupta 92.05%
Sreemoyee Bhattacharya 71.15%
Sreeparna Majumder 70.00%
Sriyanka Dasgupta 80.77%
Sudipta Beuria 77.33%
Sudipta Mukherjee 83.87%
Sukanya Roy 76.09%
Sumedha Dey 81.82%
Sumit Chakraborty 81.11%
Sumit Dey 85.25%
Sumit Mondal 81.58%
Sumit Sircar 91.38%
Sushmita Mukherjee 82.61%
Susmita Das 80.43%
Swapnil Tiwari 93.40%
Swatilekha Ghosh 69.57%
Tanima Kasyapi 74.03%
Tanusri Chakravorty 76.25%
Tarun Gogoi 82.61%
Tinni Das 89.80%
Trina Bhowmick 72.80%
Umesh Gurung 83.19%
SREENATH JHA 79.55%
Pema Choden 87.10%
Aritra Roy 91.43%
Ashik Hossain 87.10%
Nidhi Mahendru 80.77%
Dibyaraj Paul 74.19%
Anjita Ganguly 78.57%
Binay Kumar Chhetri 89.66%
Jonty Sukhvinder Jaswal 75.17%
Aman Modi 86.02%
Bibhuti Pandey 88.10%
Pratik Chaurasia 75.84%
Priyanka Bhattacharjee 89.09%
Pranav Kumar Sharma 76.71%
ARKAYAN GUHA 94.29%
Ashutosh Lodhekar 73.96%
Sweta Das 88.89%
Anabil Chakraborty 78.33%
Aditya Rupendra kumar Padmawa 86.44%
Paulami Deb 90.70%
Bodhisatta Chatterjee 84.29%
Nicholas Vicky Rozario 83.51%
Dipayan Basu 81.01%
Ranajoy Sabood 73.02%
Sushama Pahade 65.48%
SOUVIK MUKHERJEE 75.38%
Subhro Dey 87.84%
Chandradeo Jha 79.73%
Dhiraj Godbole 81.18%
Sandip Pal 79.76%
Himadri Das 79.69%
Nishit Baskey 74.63%
Chandrima Dey 78.72%
Sharmistha Roy Chowdhury 84.47%
Mohsin Akhthar 68.75%
Ashwini Nimje 79.07%
Abhranil Sengupta 83.78%
Ajinkya Shyam Gawai 78.57%
Kazim Ali Iqbal 80.00%
Sankritya Satyam 60.71%
Suman kolay 86.96%
Ranjan Mitra 71.79%
Shankar Kumar Turha 85.14%
Richa Prakash Vardani 72.22%
Faraz Sana 71.43%
Bhavesh Kanhaiyalal Lakhwani 82.69%
Abhishek Debnath 78.26%
Nikhita Jagdish Shegaonkar 68.75%
Charanjeet Kaur 67.65%
Mohammad Danish 77.03%
Mriganka sharma 80.45%
Anindya Chakraborty 95.92%
Imran Khan 91.84%
Sandip Agarwal 84.13%
Suparna Sonal 76.32%
Saiym Reyaz 73.53%
Keya Sarkar 100.00%
Aamir Khan 100.00%
Snehashish Chatterjee 64.52%
Arghya Roy 81.25%
Kritika Shaw 67.27%
Pratibha Gupta 72.09%
Vibha Ramchandra Manghani 74.19%
Anjali Shukla 60.61%
Amit Kumar Shaw 71.67%
Arka Dey 80.33%
Gita Kumari Giri 76.92%
Piyush Sushil Mishra 78.26%
Priyanka Ray 73.33%
Sumit Ram 70.97%
Sutapa Dutta 60.71%
Lalitnarayan Shukla 77.78%
Nabanita Datta 85.71%
Promit Talukdar 94.12%
Ashit gawai 67.65%
Sudeshna Baskey 80.00%
Priyanka Gaherwar 69.39%
Nicky Agarwal 88.89%
Shachindra nath Jha 25.00%
Abhishek Roy 66.67%
Pawan kumar prasad 55.56%
Suruchi Gouda 61.11%
Pralay Basu 88.14%
T Vaishnavi 76.92%
Sanjiv Hela 78.57%
Annapurna 66.67%
Chandan Shrivastva 50.00%
Ayush Kothari 58.33%
Abul Kayum 84.62%
Grand Total 78.98%
Sorry for such a long list.
The cells start from Q14 till Q213 for percentage values and K14 to K213 for names.
What I'm looking to do is conditional format in a pivot table or lookup to show only the list of people below 82% and exclude the rest.
No idea how to do it.
Thank you in advance for any kind of help.
I'm new in excel and vlookup, so consider me a near zero knowledge in vlookup and conditional formatting.
query:
Advisor: Total CSAT%
Abhishek Deb 77.92%
AJAY KUMAR TIWARY 70.27%
DHIRAJ MITRA 88.89%
MANAS SAHA 65.85%
OLYMPIA LAHIRI 84.62%
Rumki Sanyal 72.00%
Aamir Faiz 78.13%
Aanchal Singh 85.04%
Abhishek Saha 70.10%
Abhishek Sen 85.48%
Afifa Shohab 67.74%
Ajit Kumar Sha 59.74%
Amarjeet Kour 77.05%
Amit Kumar Moulay 68.49%
Anand Kumar Gupta 70.24%
Anita Rana 86.90%
Ankita Laha 77.78%
Anuj Bhatia 82.50%
Argha Das 82.81%
Arinjay Mukherjee 94.92%
Aritra Chatterjee 82.35%
Arjun Jaiswal 81.99%
ARUNAV GHOSH 78.75%
Asish Karmakar 86.44%
Atandra Ghosh 68.29%
Avinash Barui 87.41%
Ayushman Sen 65.00%
Barkha Srivastava 52.73%
Beepasha Chettri 63.64%
Binay Das 79.45%
Bornali Guha Sarkar 76.84%
Chiranjit Roy 76.60%
Churni Chakraborty 63.27%
Debangan Kar 62.26%
Debjani Maitra 76.32%
Debjoy Das 86.67%
Dipika Pandey 84.93%
Eleseba Gurung 90.00%
Eshita Muunshi 86.46%
Hena Akram 90.63%
Himanshu Gupta 91.80%
Ishannila Biswas 86.05%
Jagriti Banik 78.95%
Kaushik Chatterjee 71.83%
Khushboo Patro 77.59%
Maitri Majumder 89.66%
Manash Pal 50.00%
Md Humayal Rashid 82.68%
Megha Singh 84.21%
Mou Mitra 73.17%
Nabanita Ghorai 75.68%
Nainy Roy Ghosh 79.41%
Neha Kumari 71.21%
Oindrilla Basu 70.24%
Parijat Sarkar 77.66%
Partho Jyoti Saikia 92.68%
Priyanka Kumari 76.79%
Priyanka Roy 67.39%
Puja Rakshit 61.54%
Purba Das 75.56%
Rahul kumar 82.73%
Raja Bera 90.91%
Rajbir Kaur Lakhotra 80.77%
RATUL DAS 81.58%
Reema Banerjee 66.67%
Rinzee Tshering Bhutia 77.46%
Riya Bhattacharya 85.29%
Rohan Behara 62.96%
Rohit Goyal 89.52%
Rubab Waheed 70.42%
S K Jangdai 76.92%
Sajal Roy 75.79%
Sandip Kumar Bhunia 51.61%
Sangeeta Rout 80.77%
Sangita Baraik 82.46%
Santanu Debnath 84.81%
Satwika Roy 90.24%
Sawdhin Kr Sarangi 78.38%
Sayontee Bagchi 74.51%
Sharmila Lama 87.12%
Shreshtha Sharma 75.47%
Shubhankar Basu 72.37%
Sneha Bhattacharyya 75.00%
Sohini Majumder 86.21%
Somali Chakraborty 84.62%
Sonia Sengupta 81.48%
Soumen Nandy 81.56%
Soumi Chakraborti 76.00%
Soumyajit Dasgupta 92.05%
Sreemoyee Bhattacharya 71.15%
Sreeparna Majumder 70.00%
Sriyanka Dasgupta 80.77%
Sudipta Beuria 77.33%
Sudipta Mukherjee 83.87%
Sukanya Roy 76.09%
Sumedha Dey 81.82%
Sumit Chakraborty 81.11%
Sumit Dey 85.25%
Sumit Mondal 81.58%
Sumit Sircar 91.38%
Sushmita Mukherjee 82.61%
Susmita Das 80.43%
Swapnil Tiwari 93.40%
Swatilekha Ghosh 69.57%
Tanima Kasyapi 74.03%
Tanusri Chakravorty 76.25%
Tarun Gogoi 82.61%
Tinni Das 89.80%
Trina Bhowmick 72.80%
Umesh Gurung 83.19%
SREENATH JHA 79.55%
Pema Choden 87.10%
Aritra Roy 91.43%
Ashik Hossain 87.10%
Nidhi Mahendru 80.77%
Dibyaraj Paul 74.19%
Anjita Ganguly 78.57%
Binay Kumar Chhetri 89.66%
Jonty Sukhvinder Jaswal 75.17%
Aman Modi 86.02%
Bibhuti Pandey 88.10%
Pratik Chaurasia 75.84%
Priyanka Bhattacharjee 89.09%
Pranav Kumar Sharma 76.71%
ARKAYAN GUHA 94.29%
Ashutosh Lodhekar 73.96%
Sweta Das 88.89%
Anabil Chakraborty 78.33%
Aditya Rupendra kumar Padmawa 86.44%
Paulami Deb 90.70%
Bodhisatta Chatterjee 84.29%
Nicholas Vicky Rozario 83.51%
Dipayan Basu 81.01%
Ranajoy Sabood 73.02%
Sushama Pahade 65.48%
SOUVIK MUKHERJEE 75.38%
Subhro Dey 87.84%
Chandradeo Jha 79.73%
Dhiraj Godbole 81.18%
Sandip Pal 79.76%
Himadri Das 79.69%
Nishit Baskey 74.63%
Chandrima Dey 78.72%
Sharmistha Roy Chowdhury 84.47%
Mohsin Akhthar 68.75%
Ashwini Nimje 79.07%
Abhranil Sengupta 83.78%
Ajinkya Shyam Gawai 78.57%
Kazim Ali Iqbal 80.00%
Sankritya Satyam 60.71%
Suman kolay 86.96%
Ranjan Mitra 71.79%
Shankar Kumar Turha 85.14%
Richa Prakash Vardani 72.22%
Faraz Sana 71.43%
Bhavesh Kanhaiyalal Lakhwani 82.69%
Abhishek Debnath 78.26%
Nikhita Jagdish Shegaonkar 68.75%
Charanjeet Kaur 67.65%
Mohammad Danish 77.03%
Mriganka sharma 80.45%
Anindya Chakraborty 95.92%
Imran Khan 91.84%
Sandip Agarwal 84.13%
Suparna Sonal 76.32%
Saiym Reyaz 73.53%
Keya Sarkar 100.00%
Aamir Khan 100.00%
Snehashish Chatterjee 64.52%
Arghya Roy 81.25%
Kritika Shaw 67.27%
Pratibha Gupta 72.09%
Vibha Ramchandra Manghani 74.19%
Anjali Shukla 60.61%
Amit Kumar Shaw 71.67%
Arka Dey 80.33%
Gita Kumari Giri 76.92%
Piyush Sushil Mishra 78.26%
Priyanka Ray 73.33%
Sumit Ram 70.97%
Sutapa Dutta 60.71%
Lalitnarayan Shukla 77.78%
Nabanita Datta 85.71%
Promit Talukdar 94.12%
Ashit gawai 67.65%
Sudeshna Baskey 80.00%
Priyanka Gaherwar 69.39%
Nicky Agarwal 88.89%
Shachindra nath Jha 25.00%
Abhishek Roy 66.67%
Pawan kumar prasad 55.56%
Suruchi Gouda 61.11%
Pralay Basu 88.14%
T Vaishnavi 76.92%
Sanjiv Hela 78.57%
Annapurna 66.67%
Chandan Shrivastva 50.00%
Ayush Kothari 58.33%
Abul Kayum 84.62%
Grand Total 78.98%
Sorry for such a long list.
The cells start from Q14 till Q213 for percentage values and K14 to K213 for names.
What I'm looking to do is conditional format in a pivot table or lookup to show only the list of people below 82% and exclude the rest.
No idea how to do it.
Thank you in advance for any kind of help.
ASKER
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you Shums, you're awesome.... :)
You're Welcome Arunav! Glad I was able to help:)
Sample workbook would be much helpful.