Need help with pulling data off one spreadsheet to another

[kesh123]

Hi guys.

I need to match reference numbers with dates. Basically:

We have a Main spreadsheet with a table with few columns.
A is reference numbers and B is delivery dates.

We also have another tab called UPS which is data with UPS deliveries.
For example references in this UPS tab are in D column and dates are in J.

So what I need, is for B cell in Main sheet to take that reference number from cell A in the same row, look for it in UPS tab in D column and if found, return date from J column in the same row >> to B cell in Main spreadsheet.
Of course I will need that to work in every cell in the column. I mean I will be just putting reference numbers in Main spreadsheet in column A and getting dates returned in column B same row (if the reference is found on UPS tab as well).

Could anyone help me with that?
That is something that would help me a lot with my work.

Thank You,
Matt.

[sandy666]

Attach example excel file

In attached Excel file try not use:

• merged cells
• unnecessary formatting like: colours, borders, aligning another than default, etc...
• unnecessary zoom

You have words to logically describe the problem

To attach an Excel file to your post,

• desensitize data
• remeber that your example should reflect structure and type of data
• click Go Advanced,
• scroll down until you see Manage Attachments,
• click that and select Browse,
• select your file and click Open,
• click Upload and you will see your attachment below Upload Files from a website
• click Close this window,
• click Submit reply

After that you should see attachment in your post

[modytrane]

Sounds like you need VLOOKUP formula with IFERROR [to avoid getting errors if the reference is not found.
Post a sample file and someone will add formula to help you.

[kesh123]

I uploaded it but still cannot attach. It is in my attachments manager but cannot add it to the post... ???

Edit: hahha it got attached after all, stupid me

[sandy666]

maybe try B2: =INDEX(ups!$D$2:$D$4,MATCH(main!A2,ups!$C$2:$C$4,0)) and drag down
If you don't want #N/A wrap formula with IFERROR(formula,"")

[kesh123]

You sir, are my hero today.
Thank You so much sandy!!!

Could I bother you some more for a brief explanation of this function you wrote?
I don't want to copy/paste only, I want to know how it works so I will be able to modify it and use on my own later on.

See, I am fresh to excel, TBH just started my first office job a week ago :D

Your help is greatly appreciated, thank You!
Matt.

[sandy666]

I think you should read about: INDEX(), MATCH(), IFERROR()
and INDEX/MATCH

I'm too lazy to retype everything here

[sandy666]

If that takes care of your original question, & to say Thanks, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
then select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED.
If you did it already - ignore it.
Thank you.

[kesh123]

I will definitely do that, but I may still need your help .

So let's say there are more sheets to look through.

What the code would look like ? Lets say same cells but sheets ups, ups2, ups3 and ups4 to look through.

I tried to figure it out with iferror, but no luck.

>>EDIT: I think I've got this. Could You check out the code and tell me if everything is right in there?

=IFERROR(INDEX(ups!$D$2:$D$4, MATCH(main!A2, ups!$C$2:$C$4, 0)), IFERROR(INDEX(ups2!$D$2:$D$4, MATCH(main!A2, ups2!$C$2:$C$4, 0)), "X") )

:D

Many thanks,
Matt.

[kesh123]

I've made a little example spreadsheet, it is attached.

But I've hit a barrier. It only finds the values if the reference number is exactly the same.
With every reference number there actually come initials, from the person dispatching the box/pallet.
So in UPS sheets they would look like: 5628052MZ, 5630001KD...

I do not know who dispatched the box so Ive only got the numbers.
In that case values are not returned.

What shall I do to overcome this ?

>>EDIT: Ah yes I forgot about something. I have a carrier field. What I need:
If any value is found in any sheet, return the name of the sheet where value has been found to the carrier field.
So if I typed in the reference number and formula would find a date for it in "UPS" sheet, the carrier cell for that row would now say "UPS".
Is it possible?

BTW. I got into this, I can not sleep. :D

>>EDIT2:
I've come up for this with the carrier field so far:

=IFERROR(IF(MATCH(main!$A3,ups!$A:A,0), "UPS", IF(MATCH(main!$A3,TFS!$A:A,0), "TFS", IF(MATCH(main!$A3,OT!$A:A,0), "OT", IF(MATCH(main!$A3,polarspeed!$A:A,0), "POLARSPEED")))),"")

But only returns value for UPS, doesnt go further. I know this is very crappy scripting of mine, so please help .


Many thanks,
Matt.

[sandy666]

This is a different problem.
Create new thread with that.

You got answer for you post#1 problem.

One problem - One thread