Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula's
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula's
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 wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula's
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" wrote: 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 wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula's
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)+2 3) 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 wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula's
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" wrote: 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 wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula's
What's in B17?
Is there an exact match (not counting case differences) in Sheet2!A1:A6500? redwing wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula's
Dave,
It worked out great! Thank you, "redwing" wrote: 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" wrote: 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 wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula's | Excel Discussion (Misc queries) | |||
formula's | Excel Discussion (Misc queries) | |||
IF formula's | Excel Discussion (Misc queries) | |||
Need Help w/Formula's | Excel Worksheet Functions | |||
formula's | Excel Worksheet Functions |