Use Vlookup function and take the data in cell below

[Scorpion Steve]

Morning!!
I have stumbled across a simple but tricky situation.
I am using the basic vlookup function but need to take the data from the cell below.
EG
=vlookup(a1,c1:e14,3,0)
Thats fine but say the answer were to lay in cell E9. I need to have a formula that says: take the data from the cell below (E10 in this example)
I will try to get an image put up

[/url][/IMG]

 

[Andrew Poulsom]

Try:

=INDEX(E1:E14,MATCH(A1,C1:C14,0))

 

[Scorpion Steve]

hi
that works great .. except when i have 2 values the same in that column.

it matches the first value it comes across.

Is there anyway around this? maybe using vlookup and offset together to find the vlookup value and then move 1 row down?

 

[Andrew Poulsom]

If you want to add them up:

=SUMPRODUCT(--(C1:C14=A1),E2:E15)

 

[Scorpion Steve]

Hi
i think you may have misunderstood what i was after , or i havent explained it well enough.
Im not after adding them together.

As in the original post, and img, i have used vlookup to grab data from another worksheet and populate C22.

what i need to do is then populate D22 with the data from E4.
This however cannot be a simple =E4 formula as the data is from an external source and updates each day.

Currently to populate cell C22 im using this:
=VLOOKUP(B12,[erniedata.xls]JHW!$A$1:$D$500,4,0) [this formula is from my workbooks so may not correspond exactly on the img]

using the vlookup only finds data on that row. the data for D22 is from E4.

what im asking is how can i pull the data from E4 to populate D22 using the vlookup / offset formula but without using the MATCH func as this will only match the first occurance of B12

if you need my original w/s to understand what i mean let me know as ill need to edit it for sensitive info first !!

 

[Andrew Poulsom]

The formula I originally posted should be:

=INDEX(E1:E14,MATCH(A1,C1:C14,0)+1)

 

[Scorpion Steve]

Superb work!
Thanks very much!
Always can rely on good advice!