Match values in ranges

[paata01]

Hi All,
here is what I want to do, I have Orders numbers from customers in column A and tracking numbers in Column B

for example

[TABLE="width: 315"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Order - Number[/TD]
[TD]Shipment - Tracking Number[/TD]
[/TR]
[TR]
[TD]1000-079031-224355[/TD]
[TD]9400111699000770896629[/TD]
[/TR]
[TR]
[TD]1000-078438-551504[/TD]
[TD]9400111699000770690593[/TD]
[/TR]
[TR]
[TD]1000-079019-149952[/TD]
[TD]9400111699000776382126[/TD]
[/TR]
[TR]
[TD]1000-079003-481137[/TD]
[TD]9400111699000776707851[/TD]
[/TR]
[TR]
[TD]1000-078980-791552[/TD]
[TD]9400111699000776117261[/TD]
[/TR]
[TR]
[TD]1000-078991-196564[/TD]
[TD]9400111699000776149316[/TD]
[/TR]
[TR]
[TD]1000-079003-302571[/TD]
[TD]9400111699000776145462[/TD]
[/TR]
[TR]
[TD]1000-079001-347526[/TD]
[TD]9400111699000776173557[/TD]
[/TR]
[TR]
[TD]1000-078991-632512[/TD]
[TD]9400111699000776176176[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 339"]
<colgroup><col></colgroup><tbody>[TR]
[TD]

I have another worksheet with same orders numbers but values are not in same raws as first worksheet, I want in second worksheet COLUMN B to copy tracking numbers from Column B from first worksheet and match order numbers, how do I do it with formula.

Second worksheet TRACKING NUMBERS
[TABLE="width: 390"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Order Number[/TD]
[/TR]
[TR]
[TD]1000-079081-064741[/TD]
[/TR]
[TR]
[TD]1000-079145-649790[/TD]
[/TR]
[TR]
[TD]1000-079145-070567[/TD]
[/TR]
[TR]
[TD]1000-079162-761119[/TD]
[/TR]
[TR]
[TD]1000-079183-168199[/TD]
[/TR]
[TR]
[TD]1000-079179-449375[/TD]
[/TR]
[TR]
[TD]1000-079188-853145[/TD]
[/TR]
[TR]
[TD]1000-079162-708593[/TD]
[/TR]
[TR]
[TD]1000-079197-473195[/TD]
[/TR]
[TR]
[TD]1000-079210-533389[/TD]
[/TR]
</tbody>[/TABLE]



something like find similar order number and if match copy from column B here:
Thanks!!!!!!!!

[/TD]
[/TR]
</tbody>[/TABLE]

 

[Gerald Higgins]

Perhaps using VLOOKUP, such as

=VLOOKUP(C1,A$1:B$100,2,false)

Assumptions:
First value in your second table is in cell C1.
Your original table is in the range A1:B100.

If your data is on two separate tabs, you will need to work the tab references into this formula as appropriate.

 

[paata01]

Perhaps using VLOOKUP, such as

=VLOOKUP(C1,A$1:B$100,2,false)

Assumptions:
First value in your second table is in cell C1.
Your original table is in the range A1:B100.

If your data is on two separate tabs, you will need to work the tab references into this formula as appropriate.

thanks for reply, I tried it but can't do it.

I did this way: in second worksheet TRACKING NUMBER COLUMN (which needs to be populated) =Vlookup(First value of my second table,range of first tables, and what are other values don't really understand it. Thanks again

 

[Gerald Higgins]

Can you post the range for both tables, and also the name of the sheet that contains the first table ?

PS - there is no need to quote my post

 

[C with no eyes]

As G Higgens said, you can use: =VLOOKUP(C1,'name of worksheet 1'!$A:$B,2,false)

To reference a worksheet you need ! at the end of a name. If the worksheet name has spaces you also need to enclose it in ' '

Did you know an easy way to create a formula is simply by clicking the cells or ranges you require?
Here are the steps to creating your formula in B2 assuming your second sheet starts the order numbers in A2 (I have assumed you have a headings in row 1)

Type: =vlookup(,
Click: A1 then sheet1 then A2
Type: ,
Click: sheet2 then entire columns (click and drag over the column headers excel provides e.g. A & B)
Type: 2,false) then hit enter

NB. It will put the reference for columns A:B as sheet2!A:B but you can delete the unnecessary sheet2 reference to make it look tidier.

 

[paata01]

Here is table, I did not really get it how to do. So I want to populate column D with column B tracking numbers for each corresponding order numbers, if it finds same order number copy Tracking number (column B) in column D, if no match leave it blank.
Thank you!

 

[C with no eyes]

You have not provided a table...

We seem to be going in circles.

[TABLE="width: 562"]
<tbody>[TR]
[TD]Order - Number[/TD]
[TD]Shipment - Tracking Number[/TD]
[TD]Order - Number[/TD]
[TD]Shipment - Tracking Number[/TD]
[/TR]
[TR]
[TD]1000-079031-224355[/TD]
[TD="align: right"]940011169900071[/TD]
[TD]1000-078438-551504[/TD]
[TD="align: right"]940011169900072[/TD]
[/TR]
[TR]
[TD]1000-078438-551504[/TD]
[TD="align: right"]940011169900072[/TD]
[TD]1000-078980-791552[/TD]
[TD="align: right"]940011169900075[/TD]
[/TR]
[TR]
[TD]1000-079019-149952[/TD]
[TD="align: right"]940011169900073[/TD]
[TD]1000-078991-196564[/TD]
[TD="align: right"]940011169900076[/TD]
[/TR]
[TR]
[TD]1000-079003-481137[/TD]
[TD="align: right"]940011169900074[/TD]
[TD]1000-078991-632512[/TD]
[TD="align: right"]940011169900079[/TD]
[/TR]
[TR]
[TD]1000-078980-791552[/TD]
[TD="align: right"]940011169900075[/TD]
[TD]1000-079001-347526[/TD]
[TD="align: right"]940011169900078[/TD]
[/TR]
[TR]
[TD]1000-078991-196564[/TD]
[TD="align: right"]940011169900076[/TD]
[TD]1000-079003-302571[/TD]
[TD="align: right"]940011169900077[/TD]
[/TR]
[TR]
[TD]1000-079003-302571[/TD]
[TD="align: right"]940011169900077[/TD]
[TD]1000-079003-481137[/TD]
[TD="align: right"]940011169900074[/TD]
[/TR]
[TR]
[TD]1000-079001-347526[/TD]
[TD="align: right"]940011169900078[/TD]
[TD]1000-079019-149952[/TD]
[TD="align: right"]940011169900073[/TD]
[/TR]
[TR]
[TD]1000-078991-632512[/TD]
[TD="align: right"]940011169900079[/TD]
[TD]1000-079031-224355[/TD]
[TD="align: right"]940011169900071[/TD]
[/TR]
</tbody>[/TABLE]


I had to change the tracking number as excel kept insisting on scientific notation but you get the picture.

I created this in column A to D on a single worksheet. Copy and paste the following into cell D2 then drag it down (using the little black square in the bottom right of the cell when you hover over it)

=VLOOKUP(C2,A:B,2,FALSE)

When you have done this find an order number that you know for certain is not in the first column. It should say #NA . If it does then it's working. To get rid of the error message change the formula in D2 to (and drag it down again)

=IFNA(VLOOKUP(C2,A:B,2,FALSE),"")

If your version of excel will not accept this formula use

=IFERROR(VLOOKUP(C2,A:B,2,FALSE),"")

 

[paata01]

Thank you so much! did it and worked perfectly, I was choosing only A column for second value in formula. =IFNA(VLOOKUP(C2,A:B,2,FALSE),"") works even better leaving blanks instead of errors.
Thanks again ))