Look-up from 2 excel files.

[bentxoxo]

Hi Everyone,

I'm using this formula to get item name from another excel file when I enter the item code. Am wondering if it's possible to search in 2 files and retrieve the item name. Item code will be in any of the following file.

Product Master.xlsx
or
Archive Product Master.xlsx

=IFERROR(INDEX('[Product Masterfile.xlsx]Sheet1'!$C$8:$C$65536,MATCH(L1202,'[Product Masterfile.xlsx]Sheet1'!$E$8:$E$65536,0))," ")

Thanks in advance,

 

[DanteAmor]

Try this

=IFERROR(INDEX('[Product Masterfile.xlsx]Sheet1'!$C$8:$C$65536,MATCH(L1202,'[Product Masterfile.xlsx]Sheet1'!$E$8:$E$65536,0)),IFERROR(INDEX('[Archive Product Masterfile.xlsx]Sheet1'!$C$8:$C$65536,MATCH(L1202,'[Archive Product Masterfile.xlsx]Sheet1'!$E$8:$E$65536,0)),"It does not exist in any file"))

 

[bentxoxo]

Thanks DanteAmor, Worked like a charm.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[dstepan]

Hello all

I have something similar but I'm not sure how to interpret/manipulate this to do what I need.

I have 3 files with all pertinent data on Sheet1.

[A] - H:\Accounting\OTC Services Inc\Accounting\Month End Financial Close\2019 Month End Close\ORDER BACKLOG - (this is a current month file)

- H:\Accounting\OTC Services Inc\Accounting\Month End Financial Close\2019 Month End Close\05-May\Order Backlog May 2019 (this is a prior month file of which the directory and name changes each month so I'm not sure if it can be automated without going into the code to change the directory and file name each month).
Any thoughts on this?

[C] - H:\Accounting\OTC Services Inc\Accounting\Month End Financial Close\2019 Month End Close\Order Intake - (this is a current month file)


I am trying to match the SO # on the ORDER BACKLOG file to both the "Order Backlog for May" and then the "Order Intake" files. If the SO # matches then copy the cells in Col G, H and J.
I also need to add a vlookup(?) to add to the column H if it doesn't already have a copied cell.

Below are the before and after pics of how the "ORDER BACKLOG" looks and will look after each file is matched. Below the first 3 sets of data are the data from the other 2 files.

Obviously there are many more rows than I am showing here for brevity.

For example:

[TABLE="width: 1058"]
<tbody></tbody>[/TABLE]
[TABLE="width: 700"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 12"]This is how the file "ORDER BACKLOG" looks before matching the files "Order Backlog for May 2019" and "Order Intake"[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD] I [/TD]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD] SO #[/TD]
[TD]Cust[/TD]
[TD] Price[/TD]
[TD]Status[/TD]
[TD]Date[/TD]
[TD]Descr[/TD]
[TD]%[/TD]
[TD]Cat[/TD]
[TD] Profit [/TD]
[TD]Qtr[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD] 15759-1[/TD]
[TD]ABM[/TD]
[TD="align: right"]$15,000.00[/TD]
[TD]Pending[/TD]
[TD="align: right"] 02-May-19[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD] 16751[/TD]
[TD]ABM[/TD]
[TD="align: right"]8,890.00[/TD]
[TD]Active[/TD]
[TD="align: right"] 04-Jun-19[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"] 4 [/TD]
[TD]16617[/TD]
[TD]ABM[/TD]
[TD="align: right"]$8,410.00[/TD]
[TD]Active[/TD]
[TD="align: right"] 27-Sep-18[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD] 16730-O[/TD]
[TD]ABM[/TD]
[TD="align: right"]$9,500.00[/TD]
[TD]Active[/TD]
[TD="align: right"] 23-Apr-19[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6 [/TD]
[TD]61362[/TD]
[TD]APE[/TD]
[TD="align: right"]$3,026.00[/TD]
[TD]Active[/TD]
[TD="align: right"] 27-Oct-17[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD] 61377[/TD]
[TD]APE[/TD]
[TD="align: right"]$7,500.00[/TD]
[TD]HOLD[/TD]
[TD="align: right"] 28-Nov-17[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 8"]This is how it looks after matching to the file "Order Backlog for May 2019"[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD] I [/TD]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]SO #[/TD]
[TD]Cust[/TD]
[TD]Ext Price[/TD]
[TD]Status[/TD]
[TD]Date[/TD]
[TD]Descr[/TD]
[TD]%[/TD]
[TD]Cat[/TD]
[TD] Profit [/TD]
[TD]Qtr[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]15759-1[/TD]
[TD]ABM[/TD]
[TD="align: right"]$1,500.00[/TD]
[TD]Pending[/TD]
[TD="align: right"] 02-May-19[/TD]
[TD] [/TD]
[TD]10%[/TD]
[TD]5-10[/TD]
[TD] [/TD]
[TD]Q3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]16751[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]16617[/TD]
[TD]ABM[/TD]
[TD="align: right"]$8,410.00[/TD]
[TD]Active[/TD]
[TD="align: right"] 27-Sep-18[/TD]
[TD] [/TD]
[TD]10%[/TD]
[TD]5-10[/TD]
[TD] [/TD]
[TD]Q2 2021[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]16730-O[/TD]
[TD]ABM[/TD]
[TD="align: right"]$9,500.00[/TD]
[TD]Active[/TD]
[TD="align: right"] 23-Apr-19[/TD]
[TD] [/TD]
[TD]3%[/TD]
[TD]0-5[/TD]
[TD] [/TD]
[TD]Q2 [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]61362[/TD]
[TD]APE[/TD]
[TD="align: right"]$3,026.00[/TD]
[TD]Active[/TD]
[TD="align: right"] 27-Oct-17[/TD]
[TD] [/TD]
[TD]28%[/TD]
[TD]>10[/TD]
[TD] [/TD]
[TD]Q2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]61377[/TD]
[TD]APE[/TD]
[TD="align: right"]$7,500.00[/TD]
[TD]HOLD[/TD]
[TD="align: right"] 28-Nov-17[/TD]
[TD] [/TD]
[TD]9%[/TD]
[TD]5-10[/TD]
[TD] [/TD]
[TD]Q3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 11"]This is how it looks after matching to the file "Order Backlog for May 2019" and then to the file "Order Intake"[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD] I [/TD]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]SO #[/TD]
[TD]Cust[/TD]
[TD]Price[/TD]
[TD]Status[/TD]
[TD]Date[/TD]
[TD]Descr[/TD]
[TD]%[/TD]
[TD]Cat[/TD]
[TD] Profit [/TD]
[TD]Qtr[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]15759-1[/TD]
[TD]ABM[/TD]
[TD="align: right"]$1,500.00[/TD]
[TD]Pending[/TD]
[TD="align: right"] 02-May-19[/TD]
[TD] [/TD]
[TD]10%[/TD]
[TD]5-10[/TD]
[TD] [/TD]
[TD]Q3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]16751[/TD]
[TD]ABM[/TD]
[TD="align: right"]8,990.00[/TD]
[TD]Active[/TD]
[TD="align: right"] 04-Jun-19[/TD]
[TD] [/TD]
[TD]$10.00[/TD]
[TD]5-10[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]16617[/TD]
[TD]ABM[/TD]
[TD="align: right"]$8,410.00[/TD]
[TD]Active[/TD]
[TD="align: right"] 27-Sep-18[/TD]
[TD] [/TD]
[TD]10%[/TD]
[TD]5-10[/TD]
[TD] [/TD]
[TD]Q2 2021[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]16730-O[/TD]
[TD]ABM[/TD]
[TD="align: right"]$9,500.00[/TD]
[TD]Active[/TD]
[TD="align: right"] 23-Apr-19[/TD]
[TD] [/TD]
[TD]3%[/TD]
[TD]0-5[/TD]
[TD] [/TD]
[TD]Q2 [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]61362[/TD]
[TD]APE[/TD]
[TD="align: right"]$3,026.00[/TD]
[TD]Active[/TD]
[TD="align: right"] 27-Oct-17[/TD]
[TD] [/TD]
[TD]28%[/TD]
[TD]>10[/TD]
[TD] [/TD]
[TD]Q2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]61377[/TD]
[TD]APE[/TD]
[TD="align: right"]$7,500.00[/TD]
[TD]HOLD[/TD]
[TD="align: right"] 28-Nov-17[/TD]
[TD] [/TD]
[TD]9%[/TD]
[TD]5-10[/TD]
[TD] [/TD]
[TD]Q3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"]This is the file "Order Backlog for May 2019 "[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD] H [/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]SO #[/TD]
[TD]Cust[/TD]
[TD]Price[/TD]
[TD]Status[/TD]
[TD]Date[/TD]
[TD]DescR[/TD]
[TD]Margin[/TD]
[TD] Profit [/TD]
[TD]Quarter[/TD]
[TD]Type[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]15759-1[/TD]
[TD]ABM[/TD]
[TD="align: right"]$1,500.00[/TD]
[TD]Pending[/TD]
[TD]05/02/19[/TD]
[TD][/TD]
[TD]10%[/TD]
[TD] 150.00[/TD]
[TD]Q3[/TD]
[TD]FS[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]16539[/TD]
[TD]ZYW FSP[/TD]
[TD="align: right"]$550.00[/TD]
[TD]Hold[/TD]
[TD]06/04/18[/TD]
[TD][/TD]
[TD]25%[/TD]
[TD] 137.50[/TD]
[TD]Q12020[/TD]
[TD]FS[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]16617[/TD]
[TD]ABM[/TD]
[TD="align: right"]$8,410.00[/TD]
[TD]Active[/TD]
[TD]09/27/18[/TD]
[TD][/TD]
[TD]10%[/TD]
[TD] 841.00[/TD]
[TD]Q2 2021[/TD]
[TD]FS[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]16725[/TD]
[TD]EXEL[/TD]
[TD="align: right"]$1,100.00[/TD]
[TD]Active[/TD]
[TD]04/12/19[/TD]
[TD][/TD]
[TD]10%[/TD]
[TD] 110.00[/TD]
[TD]Q3[/TD]
[TD]FS[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]16730-O[/TD]
[TD]ABM[/TD]
[TD="align: right"]$9,500.00[/TD]
[TD]Active[/TD]
[TD]04/23/19[/TD]
[TD][/TD]
[TD]3%[/TD]
[TD] 285.00[/TD]
[TD]Q2[/TD]
[TD]FS[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]16742[/TD]
[TD]OMEN[/TD]
[TD="align: right"]$576.00[/TD]
[TD]Active[/TD]
[TD]05/15/19[/TD]
[TD][/TD]
[TD]50%[/TD]
[TD] 288.00[/TD]
[TD]Q2[/TD]
[TD]FS[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]16748[/TD]
[TD]ABM[/TD]
[TD="align: right"]$1,750.00[/TD]
[TD]Pending[/TD]
[TD]05/29/19[/TD]
[TD][/TD]
[TD]10%[/TD]
[TD] 175.00[/TD]
[TD]Q2[/TD]
[TD]FS[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]61362[/TD]
[TD]APE[/TD]
[TD="align: right"]$3,026.00[/TD]
[TD]Active[/TD]
[TD]10/27/17[/TD]
[TD][/TD]
[TD]28%[/TD]
[TD] 847.28[/TD]
[TD]Q2[/TD]
[TD]Shop[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]#[/TD]
[TD]61377[/TD]
[TD]APE[/TD]
[TD="align: right"]$7,500.00[/TD]
[TD]Active[/TD]
[TD]11/28/17[/TD]
[TD][/TD]
[TD]9%[/TD]
[TD] 675.00[/TD]
[TD]Q3[/TD]
[TD]Shop[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]#[/TD]
[TD]61377FO[/TD]
[TD]APE[/TD]
[TD="align: right"]$1,391.00[/TD]
[TD]Hold[/TD]
[TD]11/29/17[/TD]
[TD][/TD]
[TD]10%[/TD]
[TD] 139.10[/TD]
[TD]Q3[/TD]
[TD]Shop[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]This is the file "Order Intake - Sheet1"[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]SO #[/TD]
[TD]Cust[/TD]
[TD]Price[/TD]
[TD]Status[/TD]
[TD]Date[/TD]
[TD]Descr[/TD]
[TD]%[/TD]
[TD]Profit[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]16478-3[/TD]
[TD]ABM[/TD]
[TD="align: right"]$1,750.00[/TD]
[TD]Pending[/TD]
[TD="align: right"] 18-Jun-19[/TD]
[TD][/TD]
[TD]10%[/TD]
[TD="align: right"]$175.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]16751[/TD]
[TD]ABM[/TD]
[TD="align: right"]$8,890.00[/TD]
[TD]Active[/TD]
[TD="align: right"] 04-Jun-19[/TD]
[TD][/TD]
[TD]10%[/TD]
[TD="align: right"]$889.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]16751-O[/TD]
[TD]ABM[/TD]
[TD="align: right"]$5,360.00[/TD]
[TD]Active[/TD]
[TD="align: right"] 04-Jun-19[/TD]
[TD][/TD]
[TD]3%[/TD]
[TD="align: right"]$160.80[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]16756[/TD]
[TD]KOOPER[/TD]
[TD="align: right"]$2,860.00[/TD]
[TD]Active[/TD]
[TD="align: right"] 12-Jun-19[/TD]
[TD][/TD]
[TD]10%[/TD]
[TD="align: right"]$286.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]16757[/TD]
[TD]ABM[/TD]
[TD="align: right"]$7,387.00[/TD]
[TD]Active[/TD]
[TD="align: right"] 19-Jun-19[/TD]
[TD][/TD]
[TD]5%[/TD]
[TD="align: right"]$369.35[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]