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]

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

Good! That is how I recommend staring to figure out provided formulas.

I then went ahead and brought in a simplified version of some of my live data and structured it identically...

This is the second step exactly.

Now the next step is figuring out what adjustments (if any) are required to the formula to work on your actual data.

Unfortunately, I am unable to click on the provided link due to security reasons (work computer).
Can you share a sample of the data where the formula isn't working just like you did in post #1?

 

[Zanith]

Can you share a sample of the data where the formula isn't working just like you did in post #1?

I started to go down this road and ended up playing with the numbers some more and I've stumbled upon the problem, but no solution yet.

A little background - I'm pulling much of this data from an SQL database using a query through excel. In the data that is populating the Cost Pool Account Set Line, and specifically the From Account and To Account columns is a mix of both account numbers and some text entries.

When the query pulls this information in it is formatting everything as Text. Once I go into the test area of the sheet I attached using the formula you helped create in post #2 and manually key in some data, it works fine. Unfortunately manually keying in the data is not an option and it looks like I might have to live with excel pulling in the data AS text for both the To and From Account columns.

Any idea how to get around THAT?

 

[Aladin Akyurek]

I started to go down this road and ended up playing with the numbers some more and I've stumbled upon the problem, but no solution yet.

A little background - I'm pulling much of this data from an SQL database using a query through excel. In the data that is populating the Cost Pool Account Set Line, and specifically the From Account and To Account columns is a mix of both account numbers and some text entries.

When the query pulls this information in it is formatting everything as Text. Once I go into the test area of the sheet I attached using the formula you helped create in post #2 and manually key in some data, it works fine. Unfortunately manually keying in the data is not an option and it looks like I might have to live with excel pulling in the data AS text for both the To and From Account columns.

Any idea how to get around THAT?

Did you try the suggestion of post #6 at all?

 

[Zanith]

Hi Aladin,

I just now had a chance to try your formula and, technically it worked, but I have so much data that it needs to parse through that it basically locked out my PC on the live spreadsheet and I had to end task the program after 20 minutes (it was at 14% complete).


I really appreciate you taking the time to help! If there is a more lightweight option you can think of, I'd love to give it a try!

 

[63falcondude]

I started to go down this road and ended up playing with the numbers some more and I've stumbled upon the problem, but no solution yet.

A little background - I'm pulling much of this data from an SQL database using a query through excel. In the data that is populating the Cost Pool Account Set Line, and specifically the From Account and To Account columns is a mix of both account numbers and some text entries.

When the query pulls this information in it is formatting everything as Text. Once I go into the test area of the sheet I attached using the formula you helped create in post #2 and manually key in some data, it works fine. Unfortunately manually keying in the data is not an option and it looks like I might have to live with excel pulling in the data AS text for both the To and From Account columns.

Any idea how to get around THAT?

Sure, you can make the text values into numbers by highlighting the columns one at a time and using Text to Columns > Finish. If you don't want to do this, you can use this formula instead:

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(SUMPRODUCT(($E$2:$E$4+0<=$A2)*($F$2:$F$4+0>=$A2)*($G$2:$G$4=B$1)),"X","")

 

[Aladin Akyurek]

Hi Aladin,

I just now had a chance to try your formula and, technically it worked, but I have so much data that it needs to parse through that it basically locked out my PC on the live spreadsheet and I had to end task the program after 20 minutes (it was at 14% complete).


I really appreciate you taking the time to help! If there is a more lightweight option you can think of, I'd love to give it a try!

The ranges in E:F of the dropbox link should be converted to true numbers.

The formula I suggested works swiftly for the dropbox sample, so care to post the formula as you really implemented it in your workbook?

 

[Zanith]

First, let me say you guys are awesome. I REALLY appreciate your help digging into this problem I'm having. Without it I wouldn't have been able to get as far as I have now. Let me narrow down what I see as the problem in achieving that and provided an example to illustrate it.

Problem: The data that I'm pulling into the spreadsheet through a query which populates the To Account and From Account columns has (mostly) numbers and some (required) text entries as well. This has been the root cause of some successful formulas already presented not functioning properly. I was slow to realize that this is what was causing the problem. Thank you for helping me to understand that.

Earlier int his thread falcon identified a working formula for data that is numerical is below. If ALL my data was numerical and defined as such when brought into the spreadsheet through a query in excel, this formula would work perfectly. I'm not sure if this can be built on with the (new) requirement of all data in this column being categorized as text.

=IF(COUNTIFS($E:$E,"<="&$A2,$F:$F,">="&$A2,$G:$G,B$1),"X","")

I've put together a revised spreadsheet in the drop box link below which includes some of these text values on Sheet 2. I apologize for not including these before (didn't realize they were the problem) but I've put some more thought into this example (it really is only a fraction of the data) and if there is a way to get the X in the proper boxes on this spreadsheet then I should be golden.

Again, thank you so much for your help!

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

 

[63falcondude]

So your actual data looks more like this? This is from the dropbox file.

Excel 2010
	A	B	C	D	E	F	G
1	Number	Fringe Base	CRU GA		From Account	To Account	Cost Pool Account Set Name
2	51101				STAT CRU GA	STAT CRU GA	CRU GA
3	CRU GA				STAT CRU OH AVI	STAT CRU OH AVI	CRU OH AVI
4	59921				51100	51200	Fringe Base
5	81100				59920	59940	Fringe Base
6	15070				71000	71999	Fringe Base
7	91101				79100	79100	Fringe Base
8	CRU OH AVI				81000	81999	Fringe Base
9					89610	89610	Fringe Base
10					89710	89710	Fringe Base
11					91100	91200	Fringe Base
12					15070	15070	Fringe Base
Sheet2

What would be the expected outcome of a working formula for rows 3 and 8?

 

[Zanith]

So your actual data looks more like this?

Correct!

What would be the expected outcome of a working formula for rows 3 and 8?

Ideal outcome would be that the table populates as follows:


Excel 2010

	A	B	C	D	E	F	G
1	Number	Fringe Base	CRU GA		From Account	To Account	Cost Pool Account Set Name
2	51101	X			STAT CRU GA	STAT CRU GA	CRU GA
3	STAT CRU GA		X		STAT CRU OH AVI	STAT CRU OH AVI	CRU OH AVI
4	59921	X			51100	51200	Fringe Base
5	81100	X			59920	59940	Fringe Base
6	15070	X			71000	71999	Fringe Base
7	91101	X			79100	79100	Fringe Base
8	STAT CRU OH AVI				81000	81999	Fringe Base
9					89610	89610	Fringe Base
10					89710	89710	Fringe Base
11					91100	91200	Fringe Base
12					15070	15070	Fringe Base

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

If we were to add CRU OH AVI into D1 then D8 would have an X.