Vlookup to return value below Search Value

[ridimo]

Hi Guys
So very new to this so please excuse my horrible Excel Grammer. I'm hoping the masters here can help me on this weird 1 bee searching for a while but couldnt find the answer.

I have 2 Worksheets that i am working off.

Worksheet 1 has a list of employee codes

Employee Code	Name
001	Peter
002	John
003	Paul

<TBODY>
</TBODY>

Worksheet 2 is a report generated from the Payroll Program.

Leave		Opening	Days	Days	Days
Code		Balance	Accrued	Taken	Due
001	Peter
	Annual Leave	13	10.5	15	8.5
002	John
	Annual Leave	17	10.5	12	15.5
003	Paul
	Annual Leave	7	7.5	1	13.5

<TBODY>
</TBODY>

Essentially I can need to be able to get a formula that will extract the Days due value for each employee by using the employee code

 

[Matty]

Try something like:

=INDEX(RangeOfInterest,MATCH(EmployeeCode,EmployeeCodeRange,0)+1)

Matty

 

[ridimo]

Hi Matty
any chance i could ask how to work this formula? as in a dummy guide coz i never worked with index before

 

[L. Howard]

From a google search.

Matty provided the 1 row offset to suit your need.

 Here’s how I think about the formula as I’m typing it in: 

=INDEX ( Column I want a return value from , MATCH ( My Lookup Value , Column I want to Lookup against , Enter “0″ )) 

- See more at: How to Use INDEX MATCH

Regard,
Howard

 

[Matty]

Hi Matty
any chance i could ask how to work this formula? as in a dummy guide coz i never worked with index before

Hi,

Whenever I don't understand what a function does, the help files are always my first port of call. That said, to briefly explain what this formula is doing...

The INDEX part captures all of the values in range referenced, which in this case is the Days Due column. INDEX will therefore hold the following values:

{0;8.5;0;0;15.5;0;0;13.5}

The MATCH part of the formula is looking for the Employee Code being looked up within the range where all employee codes exist, and will return its numerical position within that range. So, for example, if we were referencing employee 002, then MATCH would return 4 from the following values:

{"001";0;0;"002";0;0;"003";0}

Because your Days Due data is offset by one row from where the employee codes are held (i.e. Code), we have to add 1 to what MATCH returns, so we get 5. This is passed to INDEX which then returns the 5th value in its range, meaning 15.5 comes back.

Hope this helps. And welcome to MrExcel, btw! (I forgot to put this in my last post!)

Matty