Forum Discussion
Can you use AND / OR in an INDEX MATCH
Please use absolute references in formula for all except B2
H2:H24 => $H$2:$H$24 etc
Switch between relative, absolute, and mixed references (microsoft.com)
Thanks for your help! For some reason I'm still not seeing correct values after updating the absolute references and hitting Control Shift Enter. The correct return value in C4 should be H19.
- My apologies for replying to an old thread.
The tables have been formatted as and the formula has been really helpful, it's working now with a bit of tweaking and testing. Really appreciate all the help! Have a great day! - First, i agree with Peter that this should have been a new thread.
Second, I would strongly recommend you consider re-doing you workbook to have ALL entries in ONE table and then have other tabs as reports (expense report just filters all the expense lines, Income report filters all the income lines, account reports ...)
Third, I recommend you make use of the the 'Format as a Table' function on the Home menu. This will let you refer to data based on a Table reference instead of columns so instead of Income!$B:$B it might be Income[Voucher Number]. This makes it easier to work with, easier to read (once you get used to it) and then excel only looks at the data rows instead of the entire column of the worksheet (over a million rows). For the below I will pretend you format the Income table as a table called INCOME and the Expense table as a table called EXPENSE.
So a solution to the existing, with Excel 365 you should be able to use:
=SORT(VSTACK(FILTER(INCOME, INCOME[Account Number]=$B$4, ""),FILTER(EXPENSE, EXPENSE[Account Number]=$B$4))) No, I don't have a test workbook, sorry.
I am not sure why we are working at the back end of a 5 year old discussion. Wouldn't a fresh discussion with less legacy discussion be better?
Did you put together any test workbooks? Having something to work on would help.
My immediate thoughts lean towards FILTER/VSTACK/SORT but it would be nice to see an example.
That's good, because it has functions suitable for that such as VSTACK. But unfortunately, I don't have 365, so I'm tagging a few of my friends:
Which version of Excel do you have?
HansVogelaar
Hi Hans, thank you for the response. Really appreciate your efforts in providing the suggested solution. Column B in the expense & income sheets indeed contains the entry numbers.Unfortunately, it does not work. I realize that my explanation might not have been totally complete. Cell B4 is referring to the value in the drop-down list from where I can select the account number.
I've tried to tweak the formula a bit to make it point to the correct cell reference, but it still does not work.Hopefully, the below screenshots provide more insights.
In cell B9 it should always get the first possible record from the expense or income sheet that contains the selected account number.
Cell B10 will then pull the next record, B11 the record after that, etc. until the final record has been listed.
If both income and expense sheet records can be pulled chronologically, that would be awesome, but I realize that might be quite a challenge, so it's okay to list the expense records first, and after that the income records for that relevant account.
Hope this helps and thank you again! 🙂
Does column B of the Ledger Schedule sheet contain the entry numbers? If so, try
=IFNA(IF(LEFT('Ledger Schedule'!$B$4,3)="EXP", INDEX(Expenses!$B:$B,MATCH(1,(Expenses!$B:$B>'Ledger Schedule'!$B9)*(Expenses!I:I='Ledger Schedule'!$B$4),0)), INDEX(Income!$B:$B,MATCH(1,(Income!$B:$B>'Ledger Schedule'!$B9)*(Income!I:I='Ledger Schedule'!$B$4),0))), "")
Change Income to the real name of the expense sheet if necessary.
HansVogelaar SergeiBaklan
I hope one of you can help me. I have three sheets; an income sheet, expense sheet, and ledger schedule. Both income and expense sheet contain lines of transactions with entry numbers (INCxxx for income, and EXPxxx for expenses), amounts, and account numbers.
The ledger schedule lists all transactions from both income and expense sheet upon selection of a specific account from a drop-down list.
I have created an index/match formula to retrieve the information from the expense sheet, but I'd like to include the income sheet in the same formula.
My formula so far is :
=IFNA(INDEX(Expenses!$B:$B,MATCH(1,(Expenses!$B:$B>'Ledger Schedule'!$B9)*(Expenses!I:I='Ledger Schedule'!$B$4),0)),"")
Column B refers to the transaction number (which is unique for each transaction), column I the account number that is selected from the drop-down list.
Can I use the OR function to let the match formula look at either income or expense sheet and retrieve the relevant transaction? Thank you in advance!Select D4:D10 (or however far down the data go) on Sheet1.
D4 should be the active cell in the selection.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula
=VLOOKUP($A4,Test!$A$4:$D$100,4,FALSE)<=TODAY()+30
Change 100 to a larger number if you expect to populate below row 100 on the Test sheet.
Click Format...
Activate the Fill tab.
Select red.
Click OK, then click OK again.
The conditional formatting should now move with the cells if you sort the range.
- HansVogelaar, i have a follow up question to your prior response. I am trying to work with conditional formatting to highlight "Sheet1" Column "D" red when the date of "Test" Column "D" is within 30 days of the current date for the matching name in Column "A". i can get it to work but when i sort by "Sheet1 Column "A" the conditional formatting does not sort.
HansVogelaar thank you very much. i think i can make that work.
In D4:
=IFERROR(LET(n,MATCH(A4,Test!$A:$A,0),IF(INDEX(Test!$C:$C,n)>0,INDEX(Test!$B:$B,n),"")),"")
Fill down.
SergeiBaklan Can you assist with a formula. I have three sheets: Sheet 1 which is my main screen, License, which is a list of my employees, and Test which is one of my required certifications. Sheet 1 column A automatically pulls from License for my employee names. i would like for Column "D" of sheet one to pull the value of column "B" of test if there is a value in column "C" of Test and if Column "A" of Sheet 1 matches Column "A" of Test.
ive used an index and match statement but when i filter the values are not correct.
mquigley , you are welcome
- Thanks so much, that worked!
Missed that, sorry. MATCH shall be as
, MATCH( 1, ($F3$F24=B4)*( ($G3$G24 = $C$2) + ($G3$G24 = $D$2) ), 0 )* works as AND and + as OR
