Conditional Vlookup to take certain range of data only

arunav ghosh
arunav ghosh used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
Hi Arnav,

Sample workbook would be much helpful.
arunav ghoshInformation processing specialist

Author

Commented:
Managing Director/Excel VBA Developer
Distinguished Expert 2018
Commented:
You can use below formula to list all the name below 82%
=IFERROR(INDEX(A$3:A$215,SMALL(IF(B$3:B$215<82%,ROW(A$3:A$215)-MIN(ROW(A$3:A$215))+1),ROWS(D$1:D1))),"")

Open in new window

Please find attached for your reference...
Index-Match-Less-Than-80-.xlsx
arunav ghoshInformation processing specialist

Author

Commented:
Thank you Shums, you're awesome.... :)
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
You're Welcome Arunav! Glad I was able to help:)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial