VLookup with Offset Function

[Jim SWS]

I have a small array of part numbers, descriptions and
costs that I have exported from a text.file that came from
an inventory report. The text.file is in a report format
for printing. It's the only choice I have from MAS 90.

The part numbers and descriptions I can lookup, however
the costs are on the next row. Is there away to offset the
Vlookup to pickup the data from the 7th column and one row
down.

For example =VLOOKUP(C9,partcost,7,FALSE)returns the data
from G993 and I need the data from G994 . I've tried using
Offset without any luck.

C9 is reading the part number.
The data looks likes this.

G993 Part number, Description, , , , ,empty
G994 blank1,blank2,blank3,blank4,blank5,blank6,Cost

I just know it can be done..
Thanks in advance.
Jim

 

[RagDyer]

Say your data array is A1:G100,
And your lookup value is in H1,
Then try this:

=INDEX(A1:G100,MATCH(H1,A1:A100,0)+1,7)

--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

I have a small array of part numbers, descriptions and
costs that I have exported from a text.file that came from
an inventory report. The text.file is in a report format
for printing. It's the only choice I have from MAS 90.

The part numbers and descriptions I can lookup, however
the costs are on the next row. Is there away to offset the
Vlookup to pickup the data from the 7th column and one row
down.

For example =VLOOKUP(C9,partcost,7,FALSE)returns the data
from G993 and I need the data from G994 . I've tried using
Offset without any luck.

C9 is reading the part number.
The data looks likes this.

G993 Part number, Description, , , , ,empty
G994 blank1,blank2,blank3,blank4,blank5,blank6,Cost

I just know it can be done..
Thanks in advance.
Jim

 

[LEB]

See if this will work:

=INDEX(data,MATCH(C9,A1:A2,0)+1,7)

where "data" is the name assigned to the first column of
area containing your information (where the part number
would be), and the value you're looking up is in C9. The
match function finds the exact part number, and returns
the row number containing the part number. You then add 1
to that row number, since the cost is in the next row.
The 7 indicates to look in the 7th column of that next
row, and the Index function returns the value in that
cell.

LEB