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:

<tbody></tbody>

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

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>