Forum Discussion
Index match not working on 365 for mac
I discovered why you were getting #NA -- the values along row 10 are text, and your search term in cell D6 was numeric. They look the same to your eye and mine, but to Excel they ain't the same at all.
(It can be useful when you get an #NA error to break apart the larger formula to see if you can isolate the part that's not working.
But even when it's working--I only changed the one entry in row 10 to a number, not all of them -- you would get the first instance of a match on each--and that answer is from cell J22. There are a LOT of rows where "CMN0602120" is found, the first one being in row 12. (By the way, I eliminated your column headers from the search range.)
Also noticed you have breaks in the data..... if you go off now to the right (to cell AI2 (i.e., to the right of AH2) you'll see how I used FILTER to list all of the rows that meet the criterion in cell D5, and for what it's worth, they're ALL under the column that has 0.5 at the top, so MATCHing that isn't going to filter them.
Anyway, I hope this at least gets you a little further along toward your goal. Let us know if more help is needed.....
i have cleaned up the spreadsheet and added some examples. I hope this makes what im asking clearer... hard to put into words but perhaps its a three way index match?
if you have time please have look at the attached spreadsheet and let me know if you have any suggestions.
Thanks and have great weekend
i have cleaned up the spreadsheet and added some examples. I hope this makes what im asking clearer... hard to put into words but perhaps its a three way index match?
For what it's worth, putting what you're trying to do into words, specifically English words, conversational words, NOT attempting to put it into Excel lingo, is a key step for anybody in any application. Begin with a simple conversational language description, and only then, as a next step, begin to translate into "Excelese," INDEX & MATCH, or FILTER, or whatever.
In the attached I've made another attempt using FILTER to just resolve your first example. What it reveals (to me, at any rate) is a need for still more cleaning up in your data table. Putting things like "See note 4" into a column meant to hold numbers starts to create issues of data integrity (or lack thereof).
I need to do a bit more research. I know it's possible to FILTER the results of a FILTER function, to "nest" these Dynamic Array functions....what I need to do is figure out how to specifically address the "Nth" column of the first filtered array.
What actually is "E.S.P." in your context?
Is the table In this spreadsheet one you've created, or does it come from a manufacturer like this?
im away from my computer and can’t look at your sheet just yet but to answer your questions:
Im in HVAC design and these are a manufactures fan specs for several sizes of a specific model. ESP stands for External Static Pressure and is a pressure rating a piece of equipment uses to determine how much air the equipment is able to move. These are lab tested numbers. Currently when I design a system I have to reference this data manually but I am trying to automate my process as much as possible.
currently another calculation in my report tells me the model # and ESP and minimum air volume based on my hvac design calculations. I then have to pick the closest air volume in the data >= the minimum air colume. It takes me seconds manually which is why I thought it would be easy in excel but realizing it is more complicated than I originally thought. Lol.
I then have to pick the closest air volume in the data >= the minimum air colume. It takes me seconds manually which is why I thought it would be easy in excel but realizing it is more complicated than I originally thought.
It may not be all that complicated in fact, although shaving a few seconds off of what is already only seconds may not be worth much more effort. I think the challenge (for me) is understanding the data more deeply, and therefore how to navigate the maze to automate that process. You DO understand the data, and inevitably take for granted some of the process your mind goes through in guiding you to the point. The challenge is to get that out into words that make sense to me....a challenge made more difficult by the medium of this forum.
