Forum Discussion
Index match not working on 365 for mac
Hi @mathetes thanks for the reply.
The ultimate answer from the formula i am trying to write should be returning cell J28's value in D2 (with the current selections in D5,D6,D9) as this is the closet value that matches D5, D6, D9 without going over.
D5 is a model # and matched to column A
D6 is a air pressure and matched to row 10 (F10:O10)
D9 is a minimum volume of air that needs to be delivered
The particular model # chosen (D5 @ "CMN0602120") starts on A22 and has different settings for air different air volumes which is why the same model # is listed in column a multiple times (model # A22:A31). The air pressures for that particular model are listed from (F22:O31). The particular air pressure chosen (D6 @ "0.5") should find J28 1220 as this is the first and closest number to D9 with that model @# and air Pressure without going under D9 value of 1200
Hope i made it a bit clearer. I attached the file to the thread hope it works... this is not a real file just a test to make sure the formulas wok for the report i am making. I look forward to hearing from you and will go check out Filter function now.
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.
