Trying to have index and match pick data from a table (but its not a “Table”): Index = table data F11: O255 Match Reference 1 is D5 (this is a drop down list with values entered as reference in data validation, from a different part of the sheet) with Model numbers in column A11:A255Match reference 2 is D6 (this is a drop down list with values entered in data validation separated by commas) with Headers on Row F10:O10Match Reference 3 is D9 and must find 1st and Closest Match equal to or greater than D9 The formula i have been trying to get to work with not luck, is just to find the 1st match Using the first two match references D5 and D6 (see Image attached) i need this to work to but it returns a #N/A. This is after i trimmed and cleaned the data from the original table and copied the values to their current location… this trim clean did create duplicates however i will have duplicate numbers anyway which is why i want to find the 1st closets match equal to or grater than D9. All cells are formatted as “General”. Any help or advice would be greatly appreciated! I have been working on this equation for days and can’t figure it out.

Chris_Walsh It's not totally clear what result you're expecting. My best guess, an inference from the INDEX portion of your formula, is that you're hoping to retrieve one entire row from the range $A$10:$O$255, whatever row meets the two criteria set up in the pair of MATCH functions. IF that is indeed the case, you will have better luck using the newer FILTER function, which also takes multiple criteria, which you are also attempting to implement. And lucky for you, since you are working with 365 on a Mac (as am I) you'll be able to use FILTER. Here's a link to an excellent introductory video Microsoft used to introduce FILTER.https://www.youtube.com/watch?v=9I9DtFOVPIg If you need further help, unless your application is confidential or contains proprietary data, I'd ask that you post a copy of the actual spreadsheet on OneDrive or GoogleDrive, with a link pasted here that grants access to it.

mathetes 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 AD6 is a air pressure and matched to row 10 (F10:O10)D9 is a minimum volume of air that needs to be deliveredThe 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.

walshcr 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.....

mathetes 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

walshcr 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?

Index match not working on 365 for mac | Microsoft Community Hub

11 Replies

mathetes
Silver Contributor
Apr 06, 2023
Chris_Walsh

It's not totally clear what result you're expecting. My best guess, an inference from the INDEX portion of your formula, is that you're hoping to retrieve one entire row from the range $A$10:$O$255, whatever row meets the two criteria set up in the pair of MATCH functions. IF that is indeed the case, you will have better luck using the newer FILTER function, which also takes multiple criteria, which you are also attempting to implement.

And lucky for you, since you are working with 365 on a Mac (as am I) you'll be able to use FILTER. Here's a link to an excellent introductory video Microsoft used to introduce FILTER.
https://www.youtube.com/watch?v=9I9DtFOVPIg

If you need further help, unless your application is confidential or contains proprietary data, I'd ask that you post a copy of the actual spreadsheet on OneDrive or GoogleDrive, with a link pasted here that grants access to it.
- walshcr
  Copper Contributor
  Apr 06, 2023
  mathetes
  
  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.
  
  97CMN Blower Specs 1.xlsx41 KB
  - mathetes
    Silver Contributor
    Apr 06, 2023
    walshcr
    
    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.....
    97CMN Blower Specs 1_Rev.xlsx45 KB

Forum Discussion

Index match not working on 365 for mac

11 Replies

Resources