vlookup count down one cell

[dtlucius]

I understand the Vlookup formula counting over and retrieving a cell from another column. I need it count down one cell, and return the value of a cell directly below the cell it is referencing. Is there any way to do this?
In the example below I need it to find the item code A01 in cell A6 and return the value of the cell in A7, (508).

Q.O.H.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
A6 (A01)<o></o>
A7 (508)<o></o>

 

[VoG]

Try like this

=INDEX(B:B,MATCH(C1,A:A,0)+1)

 

[dtlucius]

That works perfect!!!

Thanks

one more quick question.

if it returns a NA how do i get it to return a 0?

 

[VoG]

Which version of Excel are you using?

 

[dtlucius]

2007

 

[VoG]

Try

=IFERROR(INDEX(B:B,MATCH(C1,A:A,0)+1),0)

 

[dtlucius]

Haha when i do that, everything that was returning the correct value is now FALSE

Can you see if i am missing something?

=IFERROR(""=INDEX(PA!A:A,MATCH('Pre-Build'!A6,PA!A:A,0)+1),0)

 

[VoG]

Try

=IFERROR(INDEX(PA!A:A,MATCH('Pre-Build'!A6,PA!A:A,0)+1),0)

 

[dtlucius]

that is perfect!!! I dont care what they say about you....I like you!!!!!