This is the 1st in the series about my use of Excel at work which I’ve noted in my About Page.
Here’s the problem
I have been given several sheets where I need to find specific information based on a value. Sounds easy enough for a simple lookup, right? But upon close inspection, the data within the sheet is arranged this way:
Lookup Value: Black
Here’s what they should be doing
- Keep sheets atomic so that value only appears once
- With our example to the left, Suits is one sheet and Shoes another
- Recording information this way will ensure that your data structure is easy to find and update at later stage
Note that my data to the left is not real life example. I do not work in fashion.
Additionally, the merged data groupings does not stop with ‘shoes’ e.g. there could be another group for belts, bags, socks etc. all with the value ‘black’ in it too.
So in my reality, columns would extend out to BB and rows reach the thousands.
This is what I use
So defining what I have to do in words, I need first index my data set then match my lookup value with the same value on the sheet.
In Excel, these functions are:
- Index(array, row number, [column umber])
- Match(lookup value, lookup array,[match type])
Together, they make:
Index(array, match(lookup value, lookup array,[match type]),column number)
As you can see in the problem section, my lookup may not necessarily return the value I am looking for. For example, if I am looking for Black in the Shoes group and I want the row reference (row 6), my function might return Black under Suits group (row 3)
Any other suggestions?
This function works in most cases. In my world, there’s a little bit of spot checking to make sure that I am getting what I want. Would like to hear of a better approach..?
Since I published this post, I have had 2 responses from my LinkedIn account.
Here’s the 1st response:
If you were able to update the data, I’d suggest adding a concatenated field (combining Group and Category), and then performing the MATCH on this.
However, since this is not possible, another option is to locate the top row of the Group you are searching in, and then add this (via an OFFSET) to the INDEX and MATCH formulae. For example, using the spreadsheet in your link:
Create a named range, ‘GROUP_POS’ = MATCH(I3,B2:B25,0)
(N.B. I3 defines the Group, e.g. Suits, Shoes etc)
(N.B. This assumes that the Group and Category values are mutually exclusive)
Then, update your ‘@work’ formula to:
In your example, Suits-Black would return 1, and Shoes-Black would return 6.
This is what I did with this response:
This magnificent response was from Richard Dutton, Director @ www.key-formula.com.
Here’s the 2nd response:
the problem you describe is a common one, and I have seen people enter data into Excel in this way thinking it makes things easy for a human to read and understand; but in fact makes it difficult for Excel to pull out the required data (as you have found).
To help others in this conversation: The problem contains a table describing clothes, with column headers “Category”, “Occasion”, “Season” (for illustration purposes). The problem lies in the column “Category”, since it contains /subheadings/ such as “Suits”, “Shoes”, as well as values such as “Black”, and “Brown”. The problem comes when looking for a “Black” item in the “Category” column — because there could be an entry for a black suit and another for black shoes, and searching for “Black/Work/Summer” would not necessarily return the required row.
The normal solution to this problem is to add a new column. The current “Category” column holds two types of information: Category and Colour. I would therefore have two rows:
Suits | Black | Work | Summer
Shoes | Black | Work | Summer
etc, so that you have the extra field to search on.
Perry’s tentative suggestion is to have a different sheet for each type (eg Suits, Shoes), which would also work, but in this case I would prefer to keep the data all in one sheet so that adding a new category simply means adding a new row, instead of a new sheet.
However Perry says “Attempting to fix the data is out of the question. I do not own this sheet”. Therefore I suggest adding a new column (say E); if row 1 is the headers, and row 2 contains the first category (eg “Shoes”), then in E2 enter the formula:
This says that if the ID in column A is empty, that means this row contains a “subheading” in the Category column, so in Column E this formula gives us the subheading; if the ID in column A is not empty then this is not a subheading row, so we will use the subheading from the row above. This now gives you the extra column to search on. Does that solve your problem?
[[ @Perry: Incidentally, I nearly gave up looking for the problem statement: I click on the link above, and it takes me to a page with a link saying that the problem is documented here; I click on /that/ link and I am taken to another page with a link (that does not look like a link) to enable me to download a one-page Word document that describes the problem. Too many clicks! If I might make a suggestion: the contents of the Word document could be written as text on the destination page of the link above, although it may be difficult to do that in WordPress (I am not familiar with its use). ]]
This is what I did with this response:
I must again thank these wonderful individuals who have taken their own time to solve a problem I’ve been having. Thanks guys!
These problems are documented here (Word problem statement – click on the link): Excel User 1 – Where’s Wally
The updates Excel sheet for the problem (click on the Excel User link): Excel User
Happy to hear any reader comments on this.