Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.
Free Trial

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

INDEX and MATCH - ARRAY

raj8257
7 - Meteor

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.

 

 ABCDEFGHI 
1Information Table Data set 
2Field 1Field 2Field 3Index Field 1Field 2Field 3MatchExcel INDEX + MATCH formula:
3793592ZACCR8.58A1 797977ZPIR1,856.26A2{=INDEX($D:$D,MATCH(1,(F3=$A:$A)*(G3=$B:$B)*(H3=$C:$C)+(-H3=$C:$C),0))}
4797977ZPIR1,856.26A2 805326ZACC(729.00)A5 
5805384ZACCR324.00A3 850069ZACC60.00A6 
6805413ZPI293.76A4 793597ZACCR(288.43)A7 
7805326ZACC729.00A5      
8850069ZACC60.00A6      
9793597ZACCR288.43A7      

 

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

 

 

 

2 REPLIES 2
T_Willins
14 - Magnetar
14 - Magnetar

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.

 

Index Match.jpg

 

raj8257
7 - Meteor

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

Labels
Top Solution Authors