Oct 27 2017
10:05 AM
- last edited on
Jul 25 2018
10:17 AM
by
TechCommunityAP
Oct 27 2017
10:05 AM
- last edited on
Jul 25 2018
10:17 AM
by
TechCommunityAP
Hi I have am array formula that looks like this:
=INDEX('Rebate report'!A:A,MATCH(1,('Rebate report'!A:A=A2)*('Rebate report'!B:B=B2)*('Rebate report'!C:C=C2),0),1) which works.
I want to add in an OR function for the name in column A. I will add this name in Column T. In other words the match is correct if column A or T match A2 and COL B=B2 and COL C=C2 also match the criteria
I tried using the + to add T criteria but gave me a 0
=INDEX('Rebate report'!A:A,MATCH(1,('Rebate report'!A:A=A2)*('Rebate report'!B:B=B2)*('Rebate report'!C:C=C2)+('Rebate report'!A:A=T2),0),1)
Thanks for the help!
Jan 31 2023 09:44 AM
@Hans Vogelaar @Sergei Baklan
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!
Jan 31 2023 12:37 PM
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.
Jan 31 2023 08:44 PM - edited Jan 31 2023 08:47 PM
@Hans Vogelaar
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! :)
Feb 01 2023 12:58 AM
Which version of Excel do you have?
Feb 01 2023 02:19 AM
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:
Feb 01 2023 04:39 AM
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.
Feb 01 2023 05:54 AM
No, I don't have a test workbook, sorry.
Feb 01 2023 09:53 AM
Feb 06 2023 01:23 AM
Jun 07 2023 10:14 PM
Hi Sergei, What does the INDEX function inside the MATCH function signify and How can we use INDEX inside MATCH or INDEX function?
Jun 08 2023 03:29 AM
That's the trick for the Excel which doesn't support dynamic arrays, e.g. Excel 2016. If in such Excel we use formula like
=INDEX(A:A, MATCH( 1, (B:B=2)*(C:C=3), 0 ) )
here (B:B=2)*(C:C=3) is an array and we shall use above formula as an array one, i.e. to enter with Ctrl+Shift+Enter.
However, with INDEX( (B:B=2)*(C:C=3), 0 ) which returns exactly the same array we may use formula
=INDEX(A:A, MATCH( 1, INDEX( (B:B=2)*(C:C=3), 0 ), 0 ) )
as regular one, i.e. enter it simply with Enter.
Above is not required for the Excel which supports dynamic arrays (Excel 365).
And on practice please don't use entire columns as in above sample, that significantly affects the performance. Structured tables, dynamic ranges or fixed ranges instead.
Aug 24 2023 01:13 PM
hello @Sergei Baklan
I need help to add one more condition in Index and Match function.
B2 cell formula is working well
sheet1: b3=INDEX(Sheet2!$O$2:$AJ$2488,MATCH(Sheet1!D3,Sheet2!$J$2:$J$2488,0),MATCH(Sheet1!A3,Sheet2!$O$1:$AJ$1,0))
But I need to add one more condition-date range-Workdate need to within (sheet2) Start_date and end_date
I did try to put formula as array B2==INDEX(Sheet2!$O$2:$AJ$8,MATCH(1,INDEX((Sheet1!D2=Sheet2!$J$2:$J$8)*((C2>=Sheet2!$K$2:$K$6)*(Sheet1!C2<=Sheet2!$L$2:$L$6))*(Sheet1!A2=Sheet2!$O$1:$AJ$1),0),))
But didn't work.
Below is worksheet link
Thanks Lot
Aug 24 2023 01:42 PM
That could be
=INDEX(
'Maximo Rate table'!$O$2:$AJ$2488,
MATCH( 1,
INDEX(
(D2 = 'Maximo Rate table'!$J$2:$J$2488) *
(C2 >= 'Maximo Rate table'!$K$2:$K$2488) *
(C2 <= 'Maximo Rate table'!$L$2:$L$2488),
0 ),
0 ),
MATCH(A2, 'Maximo Rate table'!$O$1:$AJ$1, 0)
)
First MATCH is for rows with all conditions, second MATCH is for columns.
Formula is in column I (I2, etc) in attached.
Jan 15 2024 01:48 PM
Jan 16 2024 12:45 PM
@ricardo2260 I want to retrieve data in these 2 tables with options, search in table A, if there is, retrieve column data, if there is none, search in table B, if found in column B, retrieve the column.
Jan 17 2024 05:18 AM
Jan 17 2024 12:13 PM