Excel Lookup Function help

Question

Excel Lookup Function help

PureMalt Products - Quality Control Lab 20

Hello,

I have an excel output from an ERP system that's not great with functionality. I need to sort the dataset using a function to separate out variables. Currently all variables go into the "TEST RESULT" Column with the "CHARACTERISTIC DESCRIPTION" column acting as the filterable column

User's image

I would like to create a new table on sheet 1 where the test result is prefiltered into the different results eg Colour, Haze, Density etc. All of this would be individual to the given "GRN/LOT/PCODE" which is an individual. I am aware the exact case is important for the Colour and ideally would use a wildcard value for colour "Colour" so that I pick up all the options

User's image

I've tried a variety of XLookups but none seem to be returning the correct values from the table they will usually return the first value for the given GRN/LOT/PCODE

=XLOOKUP(A:A&"Colour",Query1!C:C&Query1!H:H,Query1!I:I)

Query1 is the exported dataset and sheet 1 is my attempt at getting it to sort

Thanks

Sean

Ashish Mathur 99,595 Reputation points Volunteer Moderator

2025-08-22T23:22:51.4466667+00:00

Hi,

Share the download link of the Excel file and show the expected result there.
PureMalt Products - Quality Control Lab 20 Reputation points

2025-08-25T07:15:31.5133333+00:00

Hello,

Attached is a file link to the excel and an example is P065172 that I would like for the results

https://puremalt365-my.sharepoint.com/:x:/g/personal/qclab_puremalt_com/EQ-50acV6udJrlexwqy920EBcAvT7HIlwwGRK5WSNdqu_A?e=s17hig

Thanks

Sean
HansV 462K Reputation points MVP Volunteer Moderator

2025-08-25T16:10:08.7533333+00:00

What if there are multiple results? For example, Z000009706 has Colour in row 3842 and in row 3851 on the Query1 sheet, with different test results.
Ashish Mathur 99,595 Reputation points Volunteer Moderator

2025-08-25T23:33:50.0466667+00:00

I have the same questions as the one asked by Hans.
PureMalt Products - Quality Control Lab 20 Reputation points

2025-08-26T10:05:27.08+00:00

Hello both,

This will cause an issue I would be happy with simply taking the first result in that case here since the first result would be the original data. This is because in TROPOS if you update a value it simply saves a duplicate result onto and you can view both results
PureMalt Products - Quality Control Lab 20 Reputation points

2025-08-26T14:49:29.95+00:00

Hello,

This is the updated sheet with the updated lookup

https://puremalt365-my.sharepoint.com/:x:/g/personal/qclab_puremalt_com/EQ-50acV6udJrlexwqy920EBcAvT7HIlwwGRK5WSNdqu_A?e=aeUnqJ

Warning its very unstable due to the high number of calculations

The issue is Im not sure the lookups are pulling the right data out now
PureMalt Products - Quality Control Lab 20 Reputation points

2025-08-26T14:52:21.5333333+00:00

Sorry Link here

https://puremalt365-my.sharepoint.com/:x:/g/personal/qclab_puremalt_com/EcYz_AvbSVxHgOJD_IWG6W8BuFXruaoJOhICEz_dpUtSRg?e=2Uluub
PureMalt Products - Quality Control Lab 20 Reputation points

2025-08-26T14:53:21.99+00:00

Thats the current lookup: =IFNA(VLOOKUP(CH$1,CHOOSECOLS(FILTER(Query1!$C$2:$L$695681,Query1!$C$2:$C$695681=$C84),9,10),2,0),"")

Accepted answer

0 additional answers

Your answer

Ashish Mathur 99,595 Reputation points Volunteer Moderator

2025-08-22T23:22:51.4466667+00:00

Hi,

Share the download link of the Excel file and show the expected result there.
PureMalt Products - Quality Control Lab 20 Reputation points

2025-08-25T07:15:31.5133333+00:00

Hello,

Attached is a file link to the excel and an example is P065172 that I would like for the results

https://puremalt365-my.sharepoint.com/:x:/g/personal/qclab_puremalt_com/EQ-50acV6udJrlexwqy920EBcAvT7HIlwwGRK5WSNdqu_A?e=s17hig

Thanks

Sean
HansV 462K Reputation points MVP Volunteer Moderator

2025-08-25T16:10:08.7533333+00:00

What if there are multiple results? For example, Z000009706 has Colour in row 3842 and in row 3851 on the Query1 sheet, with different test results.
Ashish Mathur 99,595 Reputation points Volunteer Moderator

2025-08-25T23:33:50.0466667+00:00

I have the same questions as the one asked by Hans.
PureMalt Products - Quality Control Lab 20 Reputation points

2025-08-26T10:05:27.08+00:00

Hello both,

This will cause an issue I would be happy with simply taking the first result in that case here since the first result would be the original data. This is because in TROPOS if you update a value it simply saves a duplicate result onto and you can view both results
PureMalt Products - Quality Control Lab 20 Reputation points

2025-08-26T14:49:29.95+00:00

Hello,

This is the updated sheet with the updated lookup

https://puremalt365-my.sharepoint.com/:x:/g/personal/qclab_puremalt_com/EQ-50acV6udJrlexwqy920EBcAvT7HIlwwGRK5WSNdqu_A?e=aeUnqJ

Warning its very unstable due to the high number of calculations

The issue is Im not sure the lookups are pulling the right data out now
PureMalt Products - Quality Control Lab 20 Reputation points

2025-08-26T14:52:21.5333333+00:00

Sorry Link here

https://puremalt365-my.sharepoint.com/:x:/g/personal/qclab_puremalt_com/EcYz_AvbSVxHgOJD_IWG6W8BuFXruaoJOhICEz_dpUtSRg?e=2Uluub
PureMalt Products - Quality Control Lab 20 Reputation points

2025-08-26T14:53:21.99+00:00

Thats the current lookup: =IFNA(VLOOKUP(CH$1,CHOOSECOLS(FILTER(Query1!$C$2:$L$695681,Query1!$C$2:$C$695681=$C84),9,10),2,0),"")

Answer 1

Barry Schwarz 3,916

The secret is to build sub-arrays of the data in Query1 that you can then perform lookups on. The FILTER function lets you select all the rows for an individual. The CHOOSECOLS function lets you concentrate on the Description and the Result.

In all the following formulas, replace xxx with row number of the last line of data in Query1.

Start by building the column headers for your new table:

In A1 enter =Query1!C1:E1
In D1 enter =TRANSPOSE(SORT(UNIQUE(Query1!H2:Hxxx)))

Generate a row for each individual:

In A2 enter =UNIQUE(Query1!C2:Cxxx)

Extract the data for the first individual:

In B2 enter =VLOOKUP($A2,FILTER(Query1!$C$2:$L$xxx,Query1!$C$2:$C$xxx=$A2),2)
Copy this cell into C2 and change the column reference from 2 to 3.
In D2 enter =VLOOKUP(D$1,CHOOSECOLS(FILTER(Query1!$C$2:$L$xxx,Query1!$C$2:$C$xxx=$A2),6,7),2,0)
Copy this cell into E2, F2, ... to the last column with a header in row 1
Copy all of row 2 starting in column B down to the last row with an individual in column A

PureMalt Products - Quality Control Lab 20 Reputation points

2025-08-25T15:49:43.27+00:00

Hello,

I've copied in what you had suggested and updated the column numbers to reflect the sheet

I know I have a number wrong somewhere but it takes ca 5 mins for the excel sheet to update so if you can see it quickly please let me know

Thanks

Sean
Barry Schwarz 3,916 Reputation points

2025-08-26T01:08:47.2933333+00:00

The first problem is you don't have 1M rows. FILTER requires the correct number.

A second problem may be that you specified columns 11 and 12. Since the filter starts in column C, this corresponds to Query1 columns M and N which you have not shown us. In the downloaded Query1, the description and result were in columns H and I

The Query1 worksheet you show in this picture is different than the one you gave us to download.

The original Query1 worksheet had over 8M active cells. Doing anything, even Open and Close, takes forever.

Sheet1 is even worse with over 12M cells. In the same workbook, this becomes intolerable.

You have 217 distinct descriptors and over 45K distinct individuals. Complicating the situation is the fact that some individuals have multiple types, e.g. automobile and medical. It looks like these varied test should be in distinct reports.

The average individual will have only 15 results which means most of the 217 cells in a row will be devoid of data and you will need to hunt for for meaningful cells. This is not a great way to view data.

It might be a good idea to take a step back from your current design and tell us what your real intent is. For example, if you don't need to count or average and particular result, using an entire column for it may not be the best approach. We don't know.
PureMalt Products - Quality Control Lab 20 Reputation points

2025-08-26T09:57:45.64+00:00

Hello,

apologies I have been doing some work in the background and updating the sheet alongside this which isn't ideal

This is an output from TROPOS which is an ERP system made by Epicor. The issue is that TROPOS doesn't allow for any data output in a meaningful way so that you could view for example all of one product colours. I can only output all of the data from TROPOS into one sheet which is why we have 45K entries as that is the entire database for the last 8 years

The other issue is that TROPOS does not distinguish the different tests when it outputs so can only output them all in the same column. This makes it unusable in say power BI since I need to do all the filtering. Idea here was I build the columns in the excel and update that once a week then simply work in power BI with the data

I'm thinking that this may be impossible in its current approach.

Apologies for th filtering mistake I think I need to work out what the code you have supplied is doing rather than just bulling forward but I'm under some time constraints on how long I spend on this. I feel it will be valuable long term to have this data available

Thanks
PureMalt Products - Quality Control Lab 20 Reputation points

2025-08-26T10:02:43.4866667+00:00

Hello,

also the 217 Descriptors is that they have all been entered into TROPOS individually over the last 8 years with no regard for consistency in naming so most will be duplicate I hope to combine them into a more manageable number but need to see what I have first. That's mostly why I need code that will do the bulk automatically because as TROPOS updates it will need to update to suit
PureMalt Products - Quality Control Lab 20 Reputation points

2025-08-26T14:10:31.5666667+00:00

Hello,

Looks like it is sorted now we are cooking by gas for sure

Issues with stability since the sheet has a tendency to crash but that's ok

Ill figure out how to pull through the values into power Bi and that should help

Thank you for all your help
PureMalt Products - Quality Control Lab 20 Reputation points

2025-08-26T14:41:23.5+00:00

Ah actually something else going on its picking up some odd data

For example same colour for all of these but should be different

Thats the current lookup: =IFNA(VLOOKUP(CH$1,CHOOSECOLS(FILTER(Query1!$C$2:$L$695681,Query1!$C$2:$C$695681=$C84),9,10),2,0),"")

Thanks

Sean
PureMalt Products - Quality Control Lab 20 Reputation points

2025-08-26T14:52:40.22+00:00

Thats the link now to the updated file

https://puremalt365-my.sharepoint.com/:x:/g/personal/qclab_puremalt_com/EcYz_AvbSVxHgOJD_IWG6W8BuFXruaoJOhICEz_dpUtSRg?e=2Uluub
Barry Schwarz 3,916 Reputation points

2025-08-27T00:22:54.74+00:00

When I was working with your previous download, I copied formulas from row 2 down to row 47xxx and all the values were repeated form row 2 until excel finished recalculating. A really long wait. I downloaded the new version and am in the process of opening it. After several minutes, there is entry in the status bar at the very bottom of the spreadsheet that says it is 6% complete. The bottom line seems to be that this approach will not work unless you get a much faster CPU. It is now much later and column CH does not have this problem.

If the output from TROPOS is plain text, maybe you could solve part of the problem by loading only a portion of the report. For example, you could load only the entries with a particular ITEM TYPE. There appear to be 8 types so hopefully the resulting spreadsheet will be only 1/8 the size.

You might want to add a SORT to the formula in C2. And for some reason you did not include row 695682 in any of your formulas.

I tried locking the cells in Query1 in the hope that Excel would realize it did not need to recalculate anything there but it did not help any.
PureMalt Products - Quality Control Lab 20 Reputation points

2025-08-27T07:23:53.4233333+00:00

Hello,

Thank you for the further attempt

You are right it seems to want to recalculate the formulas on opening which is less than ideal and also needs to calculate the rows before displaying the correct data

Thank you for the help I will mark this as solved since the lookups are correct and I can see whether its worth getting a more powerful computer for this

Thanks

Sean

Share via

Excel Lookup Function help

0 additional answers

Your answer