Microsoft for Home 2019 Excel version 2023 build 16627.20212

Question

The following formula works but is limited to querying 64 values in a single cell.&nbsp; I haveRow 1&nbsp; Col A&nbsp; &nbsp; &nbsp; &nbsp; Col B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Col C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Col DRow 2&nbsp; Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Description&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Check #&nbsp; &nbsp; &nbsp;Tax Category IdentifiedRow 3&nbsp; 1/1/2023&nbsp; Withdrawal Transfer to Loan 0004&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Loan PaymentD3 formula: limited to 64 query values per cell =IF(ISNUMBER(SEARCH("loan",B3)),"Loan Payment","*")I want to query by array to circumvent this limitation.&nbsp;&nbsp;I have played with vloopkup, match, and index but have not succeeded yet.&nbsp; If there is a formula which can do what I require, please point me to it.&nbsp; &nbsp;If the columns need to be rearranged or if any of the columns need to be resorted, please let me know.&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Sort by date low to high&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Sort A to Z&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Literal match to Col B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;CalculatedR1&nbsp; &nbsp; &nbsp; &nbsp; Col A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Col B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Col C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Col DR2&nbsp; &nbsp; &nbsp; &nbsp; THE SHERWIN WILL&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Hellenbrands&nbsp; &nbsp; Shop Supplies&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Raw MaterialR3&nbsp; &nbsp; &nbsp; &nbsp; US TREASURY&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Sherman&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Raw Material&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;TaxesR4&nbsp; &nbsp; &nbsp; &nbsp; Debit HELLENBRANDS ACE&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Treasury&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Taxes&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Shop SuppliesR5&nbsp; &nbsp; &nbsp; &nbsp; Debit USPS&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;USPS&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Office Expenses&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Office ExpensesDesired Formula for D2:D5:&nbsp; For each individual row, if the values in col A are found in Col B, return same row values from Col C to Col D.

nikolinode · Answer

D_P-971&nbsp;Maybe you can use an array formula to query by array in Excel.Here’s an example of how you can use it:=IFERROR(INDEX($C$2:$D$5,SMALL(IF($A$3=$B$2:$B$5,ROW($B$2:$B$5)-ROW($B$2)+1),ROWS($D$2:D2)),COLUMN(A1)),"")This formula will return the values from columns C and D if the value in column A matches the value in column B.You can adjust the ranges to match your data.Array formula: Ctrl+Shift+Enter.&nbsp;&nbsp;I hope that helps!NikolinoDEI know I don't know anything (Socrates)

Forum Discussion

Microsoft for Home 2019 Excel version 2023 build 16627.20212

Share

Resources