=((INDEX('AJ Code'!D2:D1001,MATCH(1,INDEX(('AJ Code'!B2:B1001=$I$3)*('AJ Code'!C2:C1001=$J$3),0,1),0))))
This is a formula I have been using for years in excel and just made the switch today to a macbook pro and I have been trying for 90 minutes to figure out how to get this to work in numbers. Any help would be greatly appreciated. Thanks in advance.
MacBook Pro with Retina display, iOS 8.1
AJ,
All I can say is that the syntax looks a mess. What is supposed to be happening there?
Jerry
I have a month and day entered in a spread sheet and I am needing to convert that to the day in the year it is. So for instance August 23 is the 235 day in the year. This formula links to a tab in vlookups both cells. If you have a simpler way to do this please let me know. Thanks for the help Jerrold.
I will try to better explain. In one tab I have 2 cells where in 1 cell is month enter numerically and the other cell is the day entered. I have the formula looking up a value that matches both of those cells and gives me a numerical value I set such as the 235 mentioned above for 8/23. So when I enter 8/24 it will pop out 236. It gets these values from a table I have created in a different tab. Hope this is a better explanation.
Hi aj,
Here is an approach that does not rely on outside tables.
If you don't care about leap years your could simplify with:
=YEARFRAC("12/31/13",A2&"/"&B2&"/14",1)×365
quinn
I should have mentioned this earlier. Sorry for forgetting to. It needs to rely on an outside table because the day in the year changes every year and I have to adjust it in the table. The table is used for when a certain payment is due and the due date changes annually.
Perhaps if you paste a small portion of your tables in here so we can see what is going on. I am shooting from the hip here, because I am not familiar with Excel but it seems that your index within your match formula is indexing a number not a column or row.
Hi aj,
I am going to join Charles in asking for a view of your tables. We also need a better description of what you are trying to accomplish.
You have a due date for a bill. This is listed in one table. How is it listed?
What do you want to happen and when? And in what table?
With a new tool can come a better way to accomplish a task. Give us the big picture.
quinn
OK, thanks again for the help guys. Here is a zoomed in screen shot. So I enter the date in cell I3 and J3 on the main tab in picture number 1. I then have it look up the number in the one of the table of cells on picture 2 which is the AJ code tab. So if it was January 22 and I was looking in the 1st table it would pull up 173. Please let me know if I can provide any further detail.
Hi aj,
I still want the big picture. This all seems needlessly complicated.
quinn
I am tracking different bills that are due either annually such as property taxes or bi annually such as car insurance. I then take the amount say it is $500 for 6 months. Lets say the formula is working and it pops out the result in cell B8. The formula would be =(b8/182.5)*500. This would then say how much of the $500 I should allocate throughout the 6 months. There may be an easier way and I open for suggestions. I am really interested in finding out how to do a vlookup with 2 criteria such as month and day. In excel you use the index with match formula. Thanks quinn.
AJ,
I quickly scanned the recent posts, so forgive me if I missed something.
Date manipulating is not too complicated. There are functions for most of the needed conversions. For instance, Year, Month and Day values can be combined to produce a Date value using the DATE function. The number of days between one date and another can be found by simple subtraction if you only need the result in Days or by using the DATEDIF function if you need other units.
Jerry
I am really interested in finding out how to do a vlookup with 2 criteria such as month and day. In excel you use the index with match formula.
You can use INDEX with MATCH in Numbers too, just the way you do in Excel. However, Numbers cannot handle Excel's array formulas. Was your original formula entered as an array formula in Excel?
SG
SG,
=((INDEX('AJ Code'!D2:D1001,MATCH(1,INDEX(('AJ Code'!B2:B1001=$I$3)*('AJ Code'!C2:C1001=$J$3),0,1),0))))
I am not sure. This is the formula. I am not sure if it is an array. All I know if when i transferred the document to number all the formulas worked except this one. Anyway to look at that formula and see what is incorrect? Thanks.
Hi aj,
Here is an approach that will tell you how much you need to have set aside on any day you happen to check.
The formula in H3 needs only the inputs in B3,C3 and D3. It combines the functions in E2-H2 into one.
=(1−(C3−TODAY())÷DAYS360(B3,C3,TRUE))×D3
Here are the formulas that got us there:
E2 = DAYS360(B2,C2,TRUE)
F2 = C2−TODAY()
G2 = 1−F2÷E2
H2 = D2×G2
You can see the pieces are simple.
quinn
Index and Match formula for numbers help
