INDEX and MATCH - ARRAY

[raj8257]

Hi,

I have a table of information. I want to return a field based on matching 3 criteria (ie Fields 1, 2, and 3). I've included the Excel Index and Match formula which I could use which provide the text string required.

	A	B	C	D	E	F	G	H	I
1	Information Table		Data set
2	Field 1	Field 2	Field 3	Index		Field 1	Field 2	Field 3	Match	Excel INDEX + MATCH formula:
3	793592	ZACCR	8.58	A1		797977	ZPIR	1,856.26	A2	{=INDEX($D:$D,MATCH(1,(F3=$A:$A)*(G3=$B:$B)*(H3=$C:$C)+(-H3=$C:$C),0))}
4	797977	ZPIR	1,856.26	A2		805326	ZACC	(729.00)	A5
5	805384	ZACCR	324.00	A3		850069	ZACC	60.00	A6
6	805413	ZPI	293.76	A4		793597	ZACCR	(288.43)	A7
7	805326	ZACC	729.00	A5
8	850069	ZACC	60.00	A6
9	793597	ZACCR	288.43	A7

The actual information table has c.250k+ rows and the data set has c.50k+ rows so using a union or append tools is going to make the output extremely large (250k x 50k).

Also please note:

* Field 3 needs to match based on whether it is either positive or negative value.

* the information table and data set are in separate excel files so need to use a tool that allows inputs from both files. Join tool may not work - there are no exclusive fields - ie Field 1 can contain the same 6 digit number more than once - hence the need to match additional fields to return the information

* Find & Replace won't work because it can't find more than 1 search criteria

[T_Willins · Accepted solution]

Hi @raj8257,

Unless I am misunderstanding the issue, I think you can use the absolute value of Field 3 and then Join the data.  Let me know if this doesn't work and we can help find a solution that works.

[raj8257]

Perfect. Thanks @T_Willins This works just fine. For some reason I thought it would be more difficult than it actually was.