Vlookup to the Left with Index and Match function

What if your lookup value is to the right of the information that you want VLOOKUP to return? Conventional wisdom says VLOOKUP cannot handle a negative column number in order to go left of the key.

You are looking up names and want the department. But the lookup table has Department on the left and Name on the Right. It would be nice if you could =VLOOKUP(A2,Table,-1,False) but you can not specify -1 as the column to return.

One solution is =VLOOKUP(I7,CHOOSE({1,2},G1:G5,F1:F5),2,0). However, I prefer to use MATCH to find where the name is located and then use INDEX to return the correct value.

The solution is to use INDEX and MATCH. =INDEX($D$4:$D$11,MATCH(A2,$E$4:$E$11,0)).

Comments

Improve your Excel Productivity with these Shortcuts and formulas

I have given below 45 tips and tricks to improve your productivity while working in Excel. Useful Keyboard Shortcuts 1. To format any selected object , press ctrl+1 2. To insert current date , press ctrl+; 3. To insert current time , press ctrl+shift+; 4. To repeat last action , press F4 5. To edit a cell comment , press shift + F2 6. To autosum selected cells , press alt + = 7. To see the suggest drop-down in a cell , press alt + down arrow 8. To enter multiple lines in a cell , press alt+enter 9. To insert a new sheet , press shift + F11 10. To edit active cell , press F2 (places cursor in the end) 11. To hide current row , press ctrl+9 12. To hide current column , press ctrl+0 13. To unhide rows in selected range , press ctrl+shift+9 14. To unhide columns in selected range , press ctrl+shift+0 15. To recalculate formulas , press F9 16. To select data in current region , press ctrl+shift+8 ...

Data Analytics

Introduction to Data Analytics What is Data Analytics? Data Analytics is the process of exploring and analyzing large data sets to help data driven decision making. Analyze Data Decision Making Definition Data when suitably filtered and analysed along with other related Data Sources and a suitable Analytics applied can provide valuable information to various organizations, industries, business, etc. in the form of prediction, recommendation, decision and the like. Applications of Data Analytics Finance & Accounting, Business analytics, Fraud , Healthcare, Information Technology, Insurance, Taxation , Internal Audit, Digital forensic, Transportation, Food, Delivery, FMCG, Planning of cities, Expenditure, Risk management, Risk detection, Security, Travelling, Managing Energy, Internet searching, Digital advertisement , etc. Real life examples of Data Analytics 1. ...

How to create a Waterfall Chart

Download Example Waterfall chart file from below link https://drive.google.com/file/d/17OKYxHKzT8NxWM0FuPqEb26ntzQqa29_/view?usp=sharing How to Create a Waterfall Chart in Excel If you want to build a waterfall chart of your own, we’ve got the step-by-step instructions for you. Although Excel 2016 includes a waterfall chart type within the chart options, if you’re working with any version older than that, you will need to construct the waterfall chart from scratch. Step 1: Create a data table Let’s start with a simple table like annual sales numbers for the current year. You will see in the table below that the sales amounts vary for each month. Some months will have positive sales growth, while others will be negative. Insert three additional columns to your Excel table to represent the movement of the columns on the waterfall chart. The base column will represent the starting point for the fall and rise of the chart. You will input all the negative numbers fro...

Solutions for Data Science

Search This Blog