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
