Get values to the left of VLOOKUP.

[jsuvman58]

I have a pivot table that can not be changed. I am using VLOOKUP on another sheet to create a different form. Is there a way to get data from the “Left” of the value that the VLOOKUP formula finds? </SPAN>

Here is a example I created. Let’s say VLOOKUP is using the “Employee ID” as its reference. How do I get it to return the “Department”? It would be nice if you could just tell it minus one (-1) and it would work. </SPAN>

I know it can be done with a macro but was wondering if there was an easier way? Maybe with the INDEX or MATCH function? I am not that familiar with these. </SPAN>

=VLOOKUP(34, B2:D5, 3, FALSE) – This would return the address of Jim Kim as you know. </SPAN>

Department</SPAN>	Employee ID</SPAN>	Name</SPAN>	Address</SPAN>
Sales</SPAN>	34</SPAN>	Jim Kim</SPAN>	Anywhere</SPAN>
Production</SPAN>	45</SPAN>	John Doe</SPAN>	Anywhere</SPAN>
Accounting</SPAN>	32</SPAN>	Jane Hatty</SPAN>	Anywhere</SPAN>
Operations</SPAN>	67</SPAN>	Joe Turner</SPAN>	Anywhere</SPAN>

<TBODY>
</TBODY>

</SPAN>

 

[VoG]

You need INDEX/MATCH like this

Excel Workbook
	A	B	C	D	E	F
1	Department	Employee ID	Name	Address		Sales
2	Sales	34	Jim Kim	Anywhere
3	Production	45	John Doe	Anywhere
4	Accounting	32	Jane Hatty	Anywhere
5	Operations	67	Joe Turner	Anywhere
Sheet2

 

[Firefly2012]

I would use INDEX/MATCH as per Vog, but there are ways to actually use VLOOKUP to do this eg:

Richard Schollar: VLOOKUP Left! | VLOOKUP WEEK

 

[jsuvman58]

Thanks VoG. I was able to use a variation of your formula and it worked great. I now understand the Index and Match a little better. I will now take a look at the option that Firefly2012 gave.