+ Reply to Thread
Results 1 to 5 of 5

Formula That Returns Multiple Values

  1. #1
    Registered User
    Join Date
    10-07-2019
    Location
    Manitoba, Canada
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    32

    Formula That Returns Multiple Values

    Hello,

    I am in need of a formula that returns multiple matches. I have tried the below formula in column J, which works if you have a single input cell.
    Please Login or Register  to view this content.
    This formula stops working when you copy it down the WB. (2000-3000 Rows,1000-2000 different lookup values).
    I want to use the value in Column B in "Table" to search "Query" and return the value in Column F.
    1 Invoice can have multiple Packing Slips which is why some appear multiple times. The "Query" table brings back every invoice, the PO #, and every packing slip associated with each PO/Invoice in our ERP.

    Here is a sample file for you to view.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Formula That Returns Multiple Values

    If you really mean it that you're using MS Office 365 ProPlus, then you should have the FILTER function, in which case you should be able to use

    =FILTER(Query!F$2:F12,Query!B$2:B$12=$B3)

    to return possibly several values from Query!F$2:F12.

    That said, I can't figure out why your formula uses Query!F$2:F12 and Query!B$2:B$12. That is, why col F expands downwards while col B remains fixed between rows 2 and 12. For that matter, there seems to be no good reason not to use table references, namely, Query[PackSlip] instead of Query!F$2:F12 and Query[Supplier PO] instead of Query!B$2:B$12 since references to table columns would automatically adjust to the actual data rows in the table.

    Putting that together with FILTER,

    Table!J3: =INDEX(FILTER(Query[PackSlip],Query[Supplier PO]=$B3),COUNTIF($B$3:$B3,$B3))

    The COUNTIF call is relatively inefficient if there'd be thousands of rows in the actual tables. Better to add a column to the table just for indexing.

    Table!K3: 1
    Table!K4: =IF(B4=B3,K3+1,1)

    Then change a formula above to

    Table!J3: =INDEX(FILTER(Query[PackSlip],Query[Supplier PO]=$B3),K3)

    Tangent: MATCH(ROW(range),ROW(range)) is also inefficient. Better in the old days to use ROW(range)-CELL("Row",range)+1, but these days better still to use SEQUENCE(ROWS(range)).

    If your workbook could be used by others with older Excel versions, would your Query table in the Query worksheet always be sorted on col F?

    ADDED: Forgot to mention that some of your invoice numbers are numeric and others are text. That's likely to cause you other problems down the road, so you should ensure all of them are text.
    Last edited by hrlngrv; 02-27-2020 at 08:19 PM.

  3. #3
    Registered User
    Join Date
    10-07-2019
    Location
    Manitoba, Canada
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    32

    Re: Formula That Returns Multiple Values

    Yes i am using 365 ProPlus and this filter formula works. Never heard of/used that before.

    That said, I can't figure out why your formula uses Query!F$2:F12
    This was just an oversight when i made the sample WB. Normally i do use table references but when i was trying to get my formula to work i thought that might be causing the error for some reason.

    I have entered the following formula and it looks to be working beautifully.
    Please Login or Register  to view this content.
    Could you give me a brief explanation about the indexing column you mentioned. I did add this in and all is well but i'm not sure i understand how this makes it more efficient.

    Also wouldn't my data need to be already sorted for this column to work? Normally i get an excel export from our ERP i copy the data and paste values into this sheet, the data is not sorted in any way at this point. Honestly if i have to remake that column formula once it's been sorted it wouldn't be the end of the world.

    One last thing, excel seems to have added the need for the "@" symbol when doing table references, sometimes it looks like [@[REFERENCE]] and sometimes its [@REFERENCE] sometimes this gets added and sometimes it does not. Could you explain the purpose of this and why it needs to be added?

    Thank you for all the help, it's much appreciated.

  4. #4
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Formula That Returns Multiple Values

    Quote Originally Posted by LtSplinter View Post
    . . . Could you give me a brief explanation about the indexing column you mentioned. . . .
    Since it appears Table!B3:Bxx would be sorted in ascending order, whenever there's a change in col B, that would mean reverting to the 1st match for the new col B value. When a col B value is the same as in the preceding row, that would mean using the next match, so adding 1 to the preceding row's col K value. This works with FILTER, which returns only those rows for which its 2nd argument array is TRUE. So FILTER does the real work, and col K indices just keep track of instance numbers for col B values.

    If your Table!B:B weren't be sorted, then you could replace the col K formulas with

    Table!K4: =COUNTIF(B$3:B4,B4)

    filled down. This is a bit like the 1st Table!J:J formulas I suggested. FILTER would still return only values from the Query table matching the Table!B:B cell on the given row, so the COUNTIF all would give the necessary index.

    The main thing to realize is that FILTER, or more precisely INDEX(FILTER(...),...), is the way of the future. INDEX(...,MATCH(...)) and even XLOOKUP are less useful.

    When sorted, the simple IF formula in col K only does 1 comparison and at most 1 addition. The COUNTIF call performs ever more comparisons in lower rows, and as many additions as there are values in its 1st argument satisfying its 2nd argument condition. That's what I meant by efficiency.

    . . . excel seems to have added the need for the "@" symbol when doing table references, sometimes it looks like [@[REFERENCE]] and sometimes its [@REFERENCE] sometimes this gets added and sometimes it does not. Could you explain the purpose of this and why it needs to be added? . . .
    I'm not the best person to answer this. @ has been used in structured references since they 1st appeared. It means current row within #Data area. As far as I know, [@[field]] and [@field] mean the same thing.

  5. #5
    Registered User
    Join Date
    10-07-2019
    Location
    Manitoba, Canada
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    32

    Re: Formula That Returns Multiple Values

    Thanks so much, this has been incredibly helpful and informative. Looks like i'm going to have to play around and figure out this "INDEX(FILTER" formula, seems incredibly useful.

    What you didn't see in my workbook is the thousands of other transaction lines and different formulas and queries i'm also using to pull information and extract necessary information to perform these lookups. My ERP data consists of column C-H and i have 9 other columns of formulas that trim and substitute characters that give me columns A,B,J. It's probably horribly inefficient but it works

    I'm going to keep it how you first suggested and if i sort my data by column E and let your formula return the correct numbers i can just copy pastevalues into another sheet and then custom sort the data between 3 different columns without breaking what your formula returned for me.

    This looks to be able to shave off hours from my process and increase the accuracy of matching these transactions to their counterparts. Thanks so much.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] CountIf Formula Returns Multiple Values (Incorrect)
    By rahul_ferns76 in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 01-30-2019, 07:36 AM
  2. [SOLVED] Index formula returns values that need to be ignored. Need syntax that excludes values.
    By ZMAFC94 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-06-2018, 11:26 PM
  3. Formula that returns only values of cells and doesnt return blank/NA values
    By pageandrewr1 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-08-2016, 12:32 PM
  4. One criteria that returns multiple values
    By ro1991 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-10-2014, 10:16 AM
  5. Formula for multiple conditions only returns values for first condition
    By Kazzza in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-01-2013, 07:36 AM
  6. Replies: 1
    Last Post: 10-23-2012, 12:08 AM
  7. Function that returns multiple values?
    By ahartman in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-14-2009, 10:43 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1