Finding a unique value in a range and returning the value in another column of the same row, without offset / slow formulae.

[AaronPMH]

Hi all, I must have been here 100 times when googling excel queries, always sorted it. This is the first time I've been stumped though. I'm a moderate excel user and just can't get my head around this one. Will try and lay it out concisely as I can.

A	B	C	D	E	F	G	H	I
		1	2	3	4	5	6	6
Data I want	ID	Title 1	Title 2	Title 3	Title 4	Title 5	Title 6	Title 7
Code AAA	0014							0014-7
Code BBB	0014						0014-6
Code CCC	0014				0014-4	0014-5
Code CCC	0014	0014-1	0014-2	0014-3

Have man handled the data so I have one unique reference in one cell for each column / period.

Logic wise, I want to return the Column & Row (really just row) reference that this unique value sits in within C3:I6 (that I have generated using the ID with the helper columns above) and return the value in column A corresponding to that row.

I got this working reasonably easily / quickly with an offset formula, however it is incredibly slow (as it's dynamic?). I have 27 columns and 200,000+ rows, likely that'll expand to more rows. (And in actual fact, 12 data columns I want to return, not just A, same formula but different return column. Lots of formula / data and the workbook already has a lot of others besides this).

Index and match should be quicker? However I run into issues as the reference is dynamic and haven't figured a way around that with offset, even turning the match funding into a column Letter and trying to push that into the formula (I recall, doing that somehow before and it being a mess, I think using INDIRECT back in the mists of time in my memory).

Also recall SUMPRODUCT can be used to turn a specific combination of values in a table range such as this, but it won't return the value I want in this setup. If it was just 1 bit of data I wanted I could place it (the value in Column A) in the C3:16 range and return that I think, combination of helper column and ID, however I have 12 of these so would be 12x27 tables / ranges I'd need to create?

Example that works ('POS Data' is the name of the worksheet).

=XLOOKUP([A Cell with the Unique reference]0014-1,OFFSET('POS DATA'!$C:$C,0,(1-[cell with the helper #])),'POS DATA'!A:A [or A:A, B:B,D:D in reality etc. 12 columns),0,1) .i.:

=XLOOKUP(0014-1,OFFSET('POS DATA'!$C:$C,0,(1-4)),'POS DATA'!A:A,0,1)

This will look for the helper column number (1,2,3,4, etc.) using offset, lookup that column and return the value in Column A where it finds the Unique ID in that column. Might have gotten the add or subtract on offset wrong here from memory but with a fiddle it works. Having foxed ranges rather than full columns would speed it up but not sure by much and causes issues with staff using it and adding rows / needing to update ranges.

Lot of waffle that may be hard to follow above- Question is how can I achieve this with a formula that won't drag the worksheet to a crawl? I have to had this off to staff to process and want to avoid giving them a formula that they need to copy, paste, run then range value to make the sheet useable again.

I tried adding out more helper columns e.g. with a max and min column helper reference (the 1-6) and using IF / AND statements but got a but lost there as well. Have filled the columns with other data "Yes", "0014" etic. rather than unique but couldn't use that either.

Feel like I'm just missing the right helper column, step of logic and a simple formula but now have lost objectivity and am frazzled.

Hope this is clear enough some 1 second send of (experienced) eyes can help me out, cheers folks.

 

[AaronPMH]

Few minor error/typos above (final helper reference should be 7 not 6, i.e. they are incremental and unique). 'ID' and 'Data I want' can all be duplicates though, that's correct.

I think I have a solution, however it was a google on SUMPRODUCT and less that pleased not fully understanding how it works. Rarely use it for this type of lookup and every time I do it's a google bodge.

=SUMPRODUCT((C3:I6="0014-2")*(ROW(A3:A6)))

This seems to give the correct row number, for the sheet not the range (Row 6, not 4). Let's say this is returned in cell 'J1' on the other worksheet.

=INDEX('POS DATA'!A:A,J1)

This then returns 'Code CCC' as the 6th Value on Column A.

Putting the full SUMPRODUCT formula in directly instead of J1 also works and saved more helper columns.

=INDEX(A:A,SUMPRODUCT((C3:I6="0014-2")*(ROW(A3:A6))))

Not sure if this is the most efficient way and will cause issues with large data, but going to play and run with it now and make sure it works in full. If there's something better, still open to it.

 

[awoohaw]

From what I read the sumproduct method is a good method. To correct the 4 v 6 row number difference you need to subract "one less than the starting row number" from the row() function. So this:

=SUMPRODUCT((C3:I6="0014-2")*(ROW(A3:A6-2)))

since row(A3)-2 >>>> 3-2 >>>> is 1​

if you want an explanation of how the formula works, ask I'll be happy to add more detail.​

 

[AaronPMH]

Thanks, got there in the end and understand what's it's doing now, just not one I use often. Unfortunately, it's still inordinately slow, perhaps even slower than the original offset solution. I still need to do further transformations after this on the output as well.

Thinking now I may Pivot the dataset above in traditional layout and pull that way. (Get the Unique ref and 12 columns in the style of A:A but putting them all in rows)

Then Pivot the other dataset to get totals by the same reference (0014-1,-2 etc.) Then just combine them with standard Xlookups.

Will solve the issue of all the duplicate fields / array / moving references - just needing formulae on each line, effectively then just a standard lookup between 2 lists.

Always founds combining / formulae based off pivots awkward, especially if it's a formula result going into a pivot, and tend to prefer to rely on straight formula in worksheet. Just looks like not an option with the breadth of this.

Hitting my knowledge cap I expect. As I said, moderate excel, so do a lot with it but generally it's modelling or transformations using the same 15-20 formulae, not this level of processing on huge datasets. Knowing the logic of what I want to do and pretty much anything being possible in Excel usually get's me there with a quick google, but going a bit beyond that.

Only a stop gap to support after a system fell over. If our IM team weren't swamped I'd toss it the the proper data folks but may hit that.

 

[awoohaw]

@AaronPMH
I am pretty sure that OFFSET is slower than SUMPRODUCT, not necessarily when you WANT to calculate something, but OFFSET recalculates with every formula change in the workbook. I am not exactly sure how volitile functions work but my impression is that a non volatile function will not recalculate if there isn't a change to something in values that affect that cell. But Volatile functions recalculate regardless.


Since you have 365, have you considered managing this process in MS-Access?

 

[AaronPMH]

Thanks, same issue with both, 20-30min to recalc and then again takes forever when filtering. Assume similar issue with both formulae. (Had similar issues in the past with with things like Max and min, long chains of countifs, array formula on large datasets etc. and a bit of google and helper columns with lighter formula usually fixed it). Turning off auto calc or running them and range valuing the data is a bit clunky as well.

Haven't used access since about 2002. Know Excel can hit a wall with large sets until it's finessed right (and I'm not up at VB, Power Query / Power Pivot level, nor relly need to be day to day) and not what I normally use if for to be honest (rare to need to do actually processing / transformation in it). Have a department that will throw things into a database and then pull / transform as needed it out with SQL, just not going to be an option with this in the time I have. May have to bite the bullet and hand it over though.

 

[AaronPMH]

Still plugging. trying to replace OFFSET with INDEX:INDEX now

 

[AaronPMH]

A	B	C	D	E	F	G	H	I
		1​	2​	3​	4​	5​	6​	7​
Data I want	ID	Title 1	Title 2	Title 3	Title 4	Title 5	Title 6	Title 7
Code AAA	14​							0014-7
Code BBB	14​						0014-6
Code CCC	14​				0014-4	0014-5
Code CCC	14​	0014-1	0014-2	0014-3
0014-5	Code CCC
5	0

Didn't mean to post the above with this, can't seem to edit.

=XLOOKUP(A9,G3:G7,A3:A7,"XXX",0,1)

Formula B8 works fine as it did before, just hard coded the lookup range rather than using offset. Trying now to replace the G3:G7 range with INDEX:INDEX so it's dynamic to the value in A9, without using OFFSET.

=XLOOKUP(A9,INDEX(A1:I1,1,A9):INDEX(A1:I1,1,A9),"XXX",0,1)

This however returns 0 instead of the expected code CCC. Do I need to specify COLUMN here somewhere? I thought INDEX with a semi-colon before or after returns the cell address.

 

[AaronPMH]

Success at last. Twigged it was a cell reference not a column reference, added helper row at the bottom of the data to define the range (prior was G2:G2, now G2:G8)

A	B	C	D	E	F	G	H	I
		1​	2​	3​	4​	5​	6​	7​
Data I want	ID	Title 1	Title 2	Title 3	Title 4	Title 5	Title 6	Title 7
Code AAA	14​							0014-7
Code BBB	14​						0014-6
Code CCC	14​				0014-4	0014-5
Code CCC	14​	0014-1	0014-2	0014-3
		1​	2​	3​	4​	5​	6​	7​
0014-5	Code CCC
5	Code CCC

XLOOKUP(A8,INDEX(A1:I1,1,MATCH(A9,A1:I1,0)):INDEX(A7:I7,1,MATCH(A9,A7:I7,0)),A2:A8,"XXXX",0,1)

Massively faster, few hundred thousand formulae in seconds, and similar lag when filtering (manageable).