Can you do index match across multiple criteria? Like index match match match?

likesmart
Posting as :
works at
You are currently posting as works at
Highlighted IconHIGHLIGHTED

The right way to do this in excel is to use sumproduct, but a lazier almost as effective way is to create concatenated columns.

like

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.

like

Ayyeee

Post Photo
likefunny
Recent IconRecent

Look into using the FILTER function

likefunny

Nice, this is solid.

like

You can do an array match to find multiple criteria {=index(X:X, match(1,(criteria=range)*(criteria2=range)...,0)

like

I’d avoid array formulas in general. But yes, it can be a workaround

like

Or xlookup using the”&” function. https://excelbuddy.com/multiple-criteria-xlookup/

likefunny

Create better “look up references” with “&”... you won’t have this issue anymore

like

Yes google index match match

funny

Index match match helps for finding intersections of a given column and row, so not more than two criteria

like

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.

like

Use filter instead it lets you do and and or type matching. And is done by ()*() and or is ()+()

like

So filter(column to filter, (criteria column = or other function criteria)*(and criteria column 2>criteria2)+(or criteria column 3 <criteria3))

like

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

like

Yes. Just use & in an array formula. That’ll match multiple criteria.

Related Posts

How do I get a job as a Mechanical Engineer if I dont have experience?

I couldn’t get an internship while in school. I have a BS in ME and projects from school under my belt. I’ve also passed the FE exam. I’ve been mostly just doing CAD for a company for the last year by creating minimally dimensioned drawings and moving parts around in assemblies. I want to evolve in my career and be doing more. I worked so hard to earn my degree and I feel like I’m not even using it. But who will hire me?

like

What is the expected non tech L4 & L5 salary range for Google Dublin? I am expecting an offer and need some benchmarks for my call with the recruiter, thanks!

like

Had a bit of a fright this morning. While I was walking Rue we crossed paths with a guy with two huskie mix dogs and they started barking at rue... we just kept walking by and then I heard the guy yell out and one of his dogs broke his leash and cane at rue. I kept my body between them until I could pick her up and at that point the dog backed off and the guy grabbed him. He asked if rue was ok but didn’t say sorry or anything 🙄 I was definitely more scared than rue, she was wagging her tail and pressing on to the park after....

like

Has anyone done a rope course in the area or Virginia? Recs?

like

Is dying your hair an unnatural color still unacceptable? To me it expresses individuality and personality

like

Hey fishes. Wanted to know your thoughts on adding recruiters on LinkedIn and messaging them for advice? E.g. is it cool for me to message an OW recruiter to help me out with my application?

like

Are the CISI qualifications worth it?

like

I am an engineer, but want to get another degree in biology. But even though it's my passion I'm worried that I won't be able to get a job quickly, I have a loan to study and I'm too young to declare bankruptcy. Any hope?

like

What’s the Nashville market like for all service lines?

like

Hello all, currently i am working at Infosys as Technology Analyst. I am a Java resource with having 5.3 years of experience.

Can anyone please suggest if there are any vacancies so that i can apply there? I am looking for a job change.

like

About to file a writ of habeas corpus for client where I.C.E. detainer is not executed and bond was paid. Will judge revoke bond if he hears about I.C.E. detainer. I’m guessing he will after I file this. Thoughts?

Is there any requirements for ETL testing in Kolkata?

4.5 yoe in audit. have opportunity to move down in rank to valuation for a tiny bit more money in lcol. with that said, i’m up for promo to manager in audit. i hate it - but if i stay i’ll make more money in the meantime and have exit ops to shitty fp&a roles. my question is: is the move to valuation provide any better exit ops? is it worth it at all this late?

like

I’m not in marketing but I love and appreciate good marketing. I don’t have TD bank, but the first commercial about staying open late and the catchy song and dancing was awesome and now the follow up of the same but remixed for banking from home.. Well played TD Bank, well played.

like

Hi friends. Freelance Senior CW (7-8 years) here, currently negotiating a 2 month freelance contract at mcgarrybowen. They want my hourly rate which is always tricky in my opinion. My typical day rate is 800/day (Atlanta, Chicago). I'm thinking of saying $100/hour, but want to be competitive. Thoughts?

(For context, I have no idea whether this is for their NYC or Chicago office.)

like

Does anyone have a template to poll a TA team on time in task? Trying to evaluate how much time a team spends on Admin duties.

like

Anyone hookup while traveling? I travel quite often and it’s tough to date seriously.

Resume/job seeking tips for someone looking to transition into tech sales as a SDR/BDR?

like

Curious - did your parents pay your college tuition fees?

likefunny

How do you navigate a working relationship where someone in a different department but at the same director level as you consistently assumes seniority over you? The thing is they're great and can be an ally, but they're frequently shutting down the team's work and having a negative impact on the team's morale and output.

like

Additional Posts in Excel Genius

Our system is really lacking and we do a lot of work outside of it to track data. Currently I’m using Excel to track data from multiple resources, however, it’s so manual and prone to error. Any suggestions to keep the data clean when I have to pull info from multiple places? I’m constantly auditing and we won’t even look at getting a new system until next year :(

How can I extract just the year into a new cell from a “yyyymmdd” cell (labeled PD here) that isn’t recognized as a date? I basically need to sort this data by year. Thanks in advance!

Post Photo
like

Tips on adjusting to Mac? I’ve figured lots of keyboard shortcuts but it’s just not the same 😪

like

I want to create a heat map of when people are unavailable with day of week as the columns and hour of day as the rows. I have start times and end times, how can I use this data to create the map?

like

Any recommend courses (coursera, edx, etc.) people would recommend for user with intermediate experience with excel?

like

Has anyone encountered this error before? We can’t access a workpaper with 70% of the work done 😩😩

Post Photo
like

I have three lists. Each is a list of processes. Each list represents one hierarchy level. Is there an easy way to get from three separate lists to one list ordered like shown on the right?
Thanks for your help fish!

Post Photo
like

Whats a good book/course that will teach me advanced excel skills? I want to be quick with shorcuts etc & learn VBA/Macros. Under $30 please, input is greatly appreciated.

like

I’ve toggled the “Show Formula” setting and it changes nothing. File format is xlsx, so that shouldn’t be the issue. Any ideas why these are showing the formula instead of the output?

Post Photo
like

Is Macabacus or any other add-in actually worth it for Excel & PPT? In LMM - MM M&A roles.

likehelpful

Best pdf to excel converter?

like

What's the F4 analogy in Office 365?

like

If you follow him on LinkedIn you probably already saw this! Lol 😆 but I totally resonate with the post... 🤣

Post Photo
funnylike

Hey Geniuses! I apologize in advance if this question has been asked before, I'm new here and just trying to learn as much as i can. Does anyone know a great source to learn Macros for beginners. I would like to create one for work. I appreciate it

like

Hi team i have a bit of doubt.
I have an excel sheet where column A is the order numbers. I want to add comments in column G.
There are many repetitions in order numbers but they are all sorted in ascending so all the repetitions are below one another.

If i have a comment, how can i automatically paste that same comment in all the repetitions of that order numbers if i write it in the 1st instance.

Thanks in advance

like

A lot of the work at my new employer is quite redundant and can be automated via macros. What are some of the best resources to learn vba?

like

Please help. I was recently hired by a Venture Capital firm that uses MacBooks instead of PCs. I’m extremely efficient with Excel on PCs and am struggling with the transition to Mac. Do any Mac users have a list of keyboard shortcuts that they use frequently, or any other tips/tricks?

likefunny

I really need some help.
What shortcut inverses my
Ctrl + [ aka Ctrl + {
which jumps through a cell to the source? How do I jump back? Manually isn't practical for a 50+ tab sheet

like

I’m trying to go back to the basics. How do you guys structure thoughts around identifying the correlation between 2 variables? A basic example: the number of steps I take in a day and the hours of sleep I got that night.

like

Just created my first macro! Super proud of myself! #humblebrag

likeuplifting

New to Fishbowl?

Download the Fishbowl app to
unlock all discussions on Fishbowl.
That was just a preview…
Sign Up to see all discussions
  • Discover what it’s like to work at companies from real professionals
  • Get candid advice from people in your field in a safe space
  • Chat and network with other professionals in your field
Sign up in seconds to unlock all discussions on Fishbowl.

Already a user?
Login here

Share

Embed this post

Copy and paste embed code on your site

Preview

Download the
Fishbowl app

See what’s happening in your industry
from the palm of your hand.

A phone with Fishbowl app

Send download link to your phone

OR

Scan your QR code to download
Fishbowl app on your mobile

By continuing you agree to Terms of Use and Privacy Policy.

Messaging rates may apply

Download app

Sign up for free to view this conversation on Fishbowl

By continuing you agree to Terms of Use and Privacy Policy

Already have an account? Log in

Sign up for free to continue using Fishbowl

By continuing you agree to Terms of Use(New) and Privacy Policy(New)
Messaging rates may apply

Already have an account? Log in

For account settings, visit Fishbowl on Desktop Browser or

General

Legal