formula's

[redwing]

using a vlookup I find the info that I am looking for.
I need to send back the contents in the cell that is in the same column but
1 row below the location of the value I found using the vlookup.

I also need to look up the same value again in another instance and return
the info that is 23 rows below the value that i find using the vlookup

 

[Dave Peterson]

Maybe...

=index(sheet2!b:b,match(a1,sheet2!a:a,0)+1)
and
=index(sheet2!b:b,match(a1,sheet2!a:a,0)+23)

or maybe

=index(sheet2!a:a,match(a1,sheet2!a:a,0)+1)
and
=index(sheet2!a:a,match(a1,sheet2!a:a,0)+23)

I'm kind of confused about what should be brought back.

 

[redwing]

Dave,
I am fairly new at these formula's in excel. Below is exactly the info that
I am looking at and trying to get back into the sheet contains all of the
address that were dumped into the Fed-x system.
I have 1000s of these shipments that go out Fed-x and their reporting system
only allows you to save it as a .txt file. When you cut and paste it into
excel it all goes into one column.

If I find the shipment number "B702" a number that is unique to each
destination.
Then I need to return the tracking # that is alway one row below the "B702" #
and when their are 2 cartons going to the same destination the second
cartons # is always 23 rows below the "B702" number.

I have honestly not yet tried what you sent back. I wanted to show you the
information below.

thank you in advance for you help.

B702 Tracking
#568372261
Store Manager FedEx Ground
Service
# 30 Franklin Packages: 2
N
1417 Franklin Mills Circle Total Weight:
74.0lbs
Payment Type:
Third Party
Philadelphia PA Carriage Value:
0
UNITED STATES 19154 Customs Value:
0
Description (1): Duties & Taxes:

References:

Dept / Notes:





MPS #: 56837226120 shipped on air waybill 568372261 Weight
source: Man-wt

 

[Dave Peterson]

If the data is in a single column, then you should use the second suggestions:

=index(sheet2!a:a,match(a1,sheet2!a:a,0)+1)
and
=index(sheet2!a:a,match(a1,sheet2!a:a,0)+23)

But if there's other stuff that trails that B702 (in the same cell), you'll have
to use something like:

=index(sheet2!a:a,match("B702"&"*",sheet2!a:a,0)+1)
and
=index(sheet2!a:a,match("B702"&"*",sheet2!a:a,0)+23)

or
=index(sheet2!a:a,match(a1&"*",sheet2!a:a,0)+1)
and
=index(sheet2!a:a,match(a1&"*",sheet2!a:a,0)+23)

(if A1 contains the B702 value.)

 

[redwing]

Dave,
I have attemped your formula listed below and continue to get back a #N/A
am I doing something wrong or do I have to try something else?

=INDEX(Sheet2!A1:A6500,MATCH($B17,Sheet2!A1:A6500,0)+1)

Thanks,

 

[Dave Peterson]

What's in B17?

Is there an exact match (not counting case differences) in Sheet2!A1:A6500?

 

[redwing]

Dave,
It worked out great!

Thank you,

Dave,
I have attemped your formula listed below and continue to get back a #N/A
am I doing something wrong or do I have to try something else?

=INDEX(Sheet2!A1:A6500,MATCH($B17,Sheet2!A1:A6500,0)+1)

Thanks,