Learn how to a build a cloud-first strategyRegister Now


Alternative of vlookup or proper adaptation

Posted on 2011-05-10
Medium Priority
Last Modified: 2012-08-13
Dear Colleagues,
I'm using the following formula:
=IF(C3<>0;IFERROR(VLOOKUP(C3;'[Coding-Index-JG.xlsx]People Coding'!$A$3:$K$169;1;0);""))

The functionality of VLOOKUP is ok but in this very case I have an issue with it because C3 is located in the B column and value to lookup is located in the A column.

To have vlookup working I just would need to move the A column to the B column and B to A but let's presume that this is for other reason is not possible.

So how Vlookup can or must be adapted to search for a value one column before and not after the main column?
I tried with INDEX and MATCH but could not figure it out.

With other words: The C3 values can be found in the B column of the other file and the respective return value needed is located in the A column which can not be moved.

Question by:Petersburg1
LVL 85

Accepted Solution

Rory Archibald earned 1000 total points
ID: 35728836
=IF(C3<>0;IFERROR(INDEX('[Coding-Index-JG.xlsx]People Coding'!$A$3:$A$169;MATCH(C3;'[Coding-Index-JG.xlsx]People Coding'!$B$3:$B$169;0));""))
should do it.

Author Closing Comment

ID: 35729584
Perfect, thanks!

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question