Related Posts
Are the CISI qualifications worth it?
Additional Posts in Excel Genius
Best pdf to excel converter?
What's the F4 analogy in Office 365?
New to Fishbowl?
Download the Fishbowl app to
unlock all discussions on Fishbowl.
unlock all discussions on Fishbowl.
The right way to do this in excel is to use sumproduct, but a lazier almost as effective way is to create concatenated columns.
You should have more likes. Sumproduct is incredibly useful
Use Index Match within an Offset. Makes for a lengthy formula, but gets work done. DM me if you face any challenges and I'll help solve it for you.
Ayyeee
Look into using the FILTER function
Nice, this is solid.
You can do an array match to find multiple criteria {=index(X:X, match(1,(criteria=range)*(criteria2=range)...,0)
I’d avoid array formulas in general. But yes, it can be a workaround
Or xlookup using the”&” function. https://excelbuddy.com/multiple-criteria-xlookup/
Create better “look up references” with “&”... you won’t have this issue anymore
Yes google index match match
Index match match helps for finding intersections of a given column and row, so not more than two criteria
Bowl Leader
If you’re saying having unique identifiers in columns (rather than having one as row label and one as column header), consider concatenating and creating a unique identifier that way.
Use filter instead it lets you do and and or type matching. And is done by ()*() and or is ()+()
So filter(column to filter, (criteria column = or other function criteria)*(and criteria column 2>criteria2)+(or criteria column 3 <criteria3))
Think of a X Y axis, that’s what index match match is. If you’re trying to do two or more criteria on the same axis, then other formulas are better
Sounds like a pivot table or if statement solution?
The answer is no - only index match match. First match references the row, and second is the column.
To accomplish multiple critierias, it is best to concatenation the unique ID in the row or column like consultant 1 mentioned. Example “Apple-Q1”
But you can't use that for the concatenate columns (I.e., the column you're pulling from won't update with the column rearrangement). Definitely agree that simpler is better. I would just lean towards simplicity by having a single "break" point rather than multiple in this case.
You could do a formula that uses multiple unique identifiers. Let's say for the row_num portion of the formula you could make multiple requirements to pull such as. MATCH(1,(COLUMN1=A1)*(COLUMN2=A2),0)
Thank you for the replies! I still haven’t figured it out. I’m trying to consolidate responses into a master tab for comparison. I’m dealing with all text, no numbers.
Each sub tab has 8 columns, with column H being the one with the data (index). The other columns are not unique identifiers. From left to right, my columns are named customer, processes, use case, data type, capability, issues with use case, in production, person x’s response.
Previously, I used the following formula:
Index(person sheet_person x response, match(master sheet_use case input,person sheet_use case column, 0))
This isn’t working because there’s no unique identifiers in the use case column. I would like to compute Person X’s response by matching customer input/customer column and use case input/use case column.
Can anyone help? Hopefully this was somewhat clear
Was able to do it using filter
Yes. Just use & in an array formula. That’ll match multiple criteria.