Help With An INDEX/MATCH Problem

anwaee2

Board Regular
Joined
Sep 13, 2012
Messages
127
Office Version
  1. 2011
Platform
  1. MacOS
I have columns on one worksheet (Sheet1!E37:F42) that has one row with blank cells. (row 41) I have a table on another sheet (Sheet2!a35:N47) with the top row having the months in columns 3 thru 14 and two rows that has blank cells (rows 36 and 46). I would like to get the month in row 35 of sheet number two that corresponds with the value in the rest of the table in sheet2 that matches the criteria in columns E and F in sheet1. I need a formula that I can copy and paste down as I will be expanding the data base. I have tried every combination of Index/Match that I knew how to do plus all the examples I could find on the internet. Some would work for one row only but them give me a #Ref or #N/A error on the rest.



Thanks for any help in advance for all on the best Excel help website.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

BristolJGM

New Member
Joined
Oct 25, 2015
Messages
30
I have columns on one worksheet (Sheet1!E37:F42) that has one row with blank cells. (row 41) I have a table on another sheet (Sheet2!a35:N47) with the top row having the months in columns 3 thru 14 and two rows that has blank cells (rows 36 and 46). I would like to get the month in row 35 of sheet number two that corresponds with the value in the rest of the table in sheet2 that matches the criteria in columns E and F in sheet1. I need a formula that I can copy and paste down as I will be expanding the data base. I have tried every combination of Index/Match that I knew how to do plus all the examples I could find on the internet. Some would work for one row only but them give me a #Ref or #N/A error on the rest.



Thanks for any help in advance for all on the best Excel help website.
Can you post an example spreadsheet?
 

anwaee2

Board Regular
Joined
Sep 13, 2012
Messages
127
Office Version
  1. 2011
Platform
  1. MacOS
How do I post an image? I can make a PDF file or I can send a sample xl file. I tried to upload both but neither would take.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,522
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How do I post an image?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

I also suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 

anwaee2

Board Regular
Joined
Sep 13, 2012
Messages
127
Office Version
  1. 2011
Platform
  1. MacOS
I downloadrd the xl2bb.xlam file and installed it to the add-ins in Excel for MAC 2011. It does not show up in any of the menus. I cannot find the Mr. Excel icon anywhere. The xl2bb.xlam file has about 79K of information in it so it is not a blank file. My other add-ins do work. Maybe it doesn't work on Excel 2011 or maybe I am not doing something right. It is definely in the add-ins as it comes up in the add-in menu.

Thank you for trying, I really appreciate all the help the Mr. Excel forum has giving me in the past.

I did figure out another way to get the largest month of the year using the formula below.

TEXT(MATCH(F37,'TOTAL CASES'!C37:N37,0)*29,"MMMM")

Thank you again.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,522
Office Version
  1. 365
Platform
  1. Windows
As far as I know XL2BB should work in Mac 2011 though I do not have a Mac to confirm. If installed correctly a new tab should be added to your Excel ribbon

1609288054851.png
 

Watch MrExcel Video

Forum statistics

Threads
1,129,717
Messages
5,637,951
Members
416,994
Latest member
cappiccolo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top