INDEX MATCH, but I need it to pull multiple results

[Zanith]

Hi all,

I'm running into an issue with some lookup I'm doing using INDEX MATCH which are stopping after they find the first result. I need this function to continue searching through the data for ALL possible matches to give me the result I need. I understand that an array formula is probably what I need, but I'm not certain and the work I've done so far to put one together has not worked properly.

The formula I'm using is as follows, and below is a rough idea of my table layout.

=IF(AND(INDEX('Cost Pool Account Set Line'!$B:$B,MATCH(Summary!C$1,'Cost Pool Account Set Line'!$D:$D,0))>=Summary!$A452,INDEX('Cost Pool Account Set Line'!$C:$C,MATCH(Summary!C$1,'Cost Pool Account Set Line'!$D:$D,0))<=Summary!$A452),"X","")

Summary = my "working" sheet which is summarizing the information.

Cost Pool Account Set Line = Table pulled in from power query on a separate sheet within the workbook


Summary Sheet Layout - I need the formula to identify that there are entries in the Cost Pool Account Set Line for LABOR not just for the 10000 (which it's doing now), but also 11000 and 11010.

Number	LABOR	FRINGE
10000	X
10100		X
11000	X
11010	X

<tbody>
</tbody>

Cost Pool Account Set Line

From Account	To Account	Cost Pool Account Set Name
10000	10099	LABOR
10100	10100	FRINGE
10999	11999	LABOR

<tbody>
</tbody>

The formula above may be overly cumbersome for what I'm trying to achieve, if there are simplification suggestions I'm happy to hear them!

Hopefully this make some sense, if not please let me know if there is additional information I can provide to help.

Thank you!

 

[63falcondude]

Try this:

Excel 2010
	A	B	C	D	E	F	G
1	Number	LABOR	FRINGE		From Account	To Account	Cost Pool Account Set Name
2	10000	X			10000	10099	LABOR
3	10100		X		10100	10100	FRINGE
4	11000	X			10999	11999	LABOR
5	11010	X
Sheet1

Cell Formulas
Range	Formula
B2	=IF(COUNTIFS($E:$E,"<="&$A2,$F:$F,">="&$A2,$G:$G,B$1),"X","")

 

[Oeldere]

Maybe with Vlookup, like this.

Book1
	A	B	C
22	Number	LABOR	FRINGE
23	10000	LABOR
24	10100	FRINGE
25	11000	LABOR
26	11010	LABOR
27
28
29	Cost Pool Account Set Line
30
31	From Account	To Account	Cost Pool Account Set Name
32	10000	10099	LABOR
33	10100	10100	FRINGE
34	10999	11999	LABOR
Blad1

Cell Formulas
Range	Formula
B23	=VLOOKUP(A23,$A$32:$C$34,3,1)

 

[Zanith]

Try this:

Worksheet Formulas Cell Formula B2 =IF(COUNTIFS($E:$E,"<="&$A2,$F:$F,">="&$A2,$G:$G,B$1),"X","") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Thanks for the response 63falcondude. I took my original formula:

=IF(AND(INDEX('Cost Pool Account Set Line'!$B:$B,MATCH(Summary!C$1,'Cost Pool Account Set Line'!$D:$D,0))>=Summary!$A452,INDEX('Cost Pool Account Set Line'!$C:$C,MATCH(Summary!C$1,'Cost Pool Account Set Line'!$D:$D,0))<=Summary!$A452),"X","")

And modified it using your suggestion to create the following:

=IF(COUNTIFS('Cost Pool Account Set Line'!$B:$B,">="&Summary!$A2,'Cost Pool Account Set Line'!$C:$C,"<="&Summary!$A2,'Cost Pool Account Set Line'!$D:$D,Summary!C$1),"X","")

I did swap the < and > signs as I need to look for the number in A2 and return an X only if it sits between the values of what's in column B and C within the Cost Pool Account Set Line. Unfortunately, this formula returning false values for me in cells where it should be returning true. In fact, I'm not getting any true values across the entire spreadsheet. Could you walk me through the thought process of the COUNTIFS above. That might help me.

Thank you!!

 

[Zanith]

Hi Oeldere,

I initially started down the vlookup road but was unable to get this working due to some of the criteria I needed to check against (is my lookup greater than this number over here, but less then this other number) and those limitations forced me to move to the INDEX MATCH formula in my OP which works, but is only returning to me the first result in situations where I know there are multiple hits.

If you have suggestions on how to make vlookup work in this situation I'd love to hear them.

Thanks!

 

[Aladin Akyurek]

Sheet2 (data)

Row\Col	A​	B​	C​
2​	10000	10099	LABOR
3​	10100	10100	FRINGE
4​	10999	11999	LABOR

Sheet1 (processing)

Row\Col	A​	B​	C​
1​	Number	LABOR	FRINGE
2​	10000	X
3​	10100		X
4​	11000	X
5​	11010	X

In B2 control+shift+enter, not just enter, copy across, and down:

=IF(ISNUMBER(MATCH(1,(INDEX(IF(Sheet2!$C$2:$C$4=B$1,Sheet2!$A$2:$B$4,-9.99E+307),0,1)<=$A2)*(INDEX(IF(Sheet2!$C$2:$C$4=B$1,Sheet2!$A$2:$B$4,-9.99E+307),0,2)>=$A2),0)),"X","")

 

[63falcondude]

Thanks for the response 63falcondude. I took my original formula:

=IF(AND(INDEX('Cost Pool Account Set Line'!$B:$B,MATCH(Summary!C$1,'Cost Pool Account Set Line'!$D:$D,0))>=Summary!$A452,INDEX('Cost Pool Account Set Line'!$C:$C,MATCH(Summary!C$1,'Cost Pool Account Set Line'!$D:$D,0))<=Summary!$A452),"X","")

And modified it using your suggestion to create the following:

=IF(COUNTIFS('Cost Pool Account Set Line'!$B:$B,">="&Summary!$A2,'Cost Pool Account Set Line'!$C:$C,"<="&Summary!$A2,'Cost Pool Account Set Line'!$D:$D,Summary!C$1),"X","")

I did swap the < and > signs as I need to look for the number in A2 and return an X only if it sits between the values of what's in column B and C within the Cost Pool Account Set Line. Unfortunately, this formula returning false values for me in cells where it should be returning true. In fact, I'm not getting any true values across the entire spreadsheet. Could you walk me through the thought process of the COUNTIFS above. That might help me.

Thank you!!

You have the inequalities backwards.
If A2 has to be between columns B and C (assuming that column B is "from account" and column C is "to account"), that means that B:B <= A2 <= C:C.

Also, make sure that your references are pointing to the correct cells (see example from post #2).

 

[raven_squire]

I do not fully understand your needs. Perhaps you could break the problem down in to smaller pieces.

I think you are after a lookup formula with complex conditions. The below formula is one I have used in the past with great success thanks to this forum. I don’t full understand it but I believe that it turns each element in an array into true and false. Each condition narrows down the array until you are left with only one result.

=LOOKUP(2,1/((Array1=Lookup Value1)*(array2=lookup value2),result array)

Or with different operators

=LOOKUP(2,1/((Array1>=Lookup Value1)*(array2<=lookup value2),result array)

I hope that this is of some help.

 

[Zanith]

Thanks 63falcondude, I appreciate the clarification on the inequalities and it makes complete sense now.

To help my understanding I went and created the table in post #2 above and used the formula you provided. Worked like a charm.

I then went ahead and brought in a simplified version of some of my live data and structured it identically, used the same formula, and ran into the same problem on the simple sheet that I'm having on my primary spreadsheet.

I'm not sure what is going on here to cause it to work fine using the example, but not using my live data.

I've uploaded the spreadsheet I mentioned in this post with the copy of post #2 on sheet 1 and sheet 2 containing the same formula but I've brought in my data for the To/From and Cost Pool Account Set Name. If you would be able to look at this and tell me what I'm doing wrong I would very much appreciate it.

https://www.dropbox.com/s/rlxg1vzlza6qmbt/COST POOL MATRIX - HELPv3.xlsx?dl=0

Thank you!!

 

[Aladin Akyurek]

See post #6...