Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Simple Function request, in Excel not VBA

  1. #1
    Board Regular
    Join Date
    Jan 2016
    Posts
    111
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Simple Function request, in Excel not VBA

    Just trying to come up with a formula that helps me do what I want to do in terms of indexing, matching, etc.

    I have a row-finding formula that works:
    =SMALL(IF($A$2:$A$102=$J2,ROW($A$2:$A$102)),ROW(1:1))

    This finds me the Row Number I want.

    I have another formula here:
    =INDEX(E10:J10,MATCH(M4:R4,E10:J10,0),0)

    Notice everywhere that says "10". How do I replace the "10" with the SMALL formula above, so that the formula can be the variable I want. I don't want the "10", I want the Small formula to find the row I want.

    I know how to do it in VBA, but the & and the "" method isn't working. Must have forgotten this or I am just missing something.

    Thanks.

  2. #2
    Board Regular
    Join Date
    Jul 2014
    Location
    Memphis, TN
    Posts
    2,677
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Simple Function request, in Excel not VBA

    I'm not a user of the function but I'm pretty sure you want to use INDIRECT in some form.
    Check the MSDN library
    Excel 2013, 2016 with PowerBI
    Knowing that it can be done is half the battle!

  3. #3
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,661
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Simple Function request, in Excel not VBA

    It might be easier if you could explain what you want as a result. There are several options. For example, if your SMALL formula is in Z1:

    =INDEX($E$2:$J$102,Z1-1,MATCH(M4:R4,OFFSET($E$2:$J$2,Z1-2,0),0)

    But if you could post a sample of your data (see the HTML Maker in my signature) with the expected results, it might be possible to come up with something better.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  4. #4
    Board Regular
    Join Date
    Jan 2016
    Posts
    111
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Simple Function request, in Excel not VBA

    Here is a screen shot.
    The goal is to type any single code from column A into Column J, and get an answer in Column K. What it is doing is finding the values of the code's row that I entered in column J, and then populating in Columns L-Q.
    When I type any code into Column J, I have indexes in Columns L-Q that populate what is in the row of the ID I entered into column J.
    This "works" as is, but as an exercise in better formula writing, I am trying to condense into one formula in Column K, vs this mess.
    Column K example formula is: =SUMPRODUCT(--(D2:I2=L2:Q2))>0 . Very simple
    Column L example formula is: =INDEX($A$2:$K$102,MATCH(J2,A:A,0)*1-1,4) . Only the last digit in Column's M-Q change from this Column L formula.


  5. #5
    Board Regular
    Join Date
    Jan 2016
    Posts
    111
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Simple Function request, in Excel not VBA

    Darn. Thought I could maybe just replace the L2:Q2 in the Column K formula, with all of the Index formulas in Columns L through M.

    =SUMPRODUCT(--(D4:I4=INDEX($A$2:$K$102,MATCH(J4,A:A,0)*1-1,4)&","&INDEX($A$2:$K$102,MATCH($J4,$A:$A,0)*1-1,5)&","&INDEX($A$2:$K$102,MATCH($J4,$A:$A,0)*1-1,6)&","&INDEX($A$2:$K$102,MATCH($J4,$A:$A,0)*1-1,7)&","&INDEX($A$2:$K$102,MATCH($J4,$A:$A,0)*1-1,8)&","&INDEX($A$2:$K$102,MATCH($J4,$A:$A,0)*1-1,9)))>0

    Something like this. Alas, doesn't work.

  6. #6
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,661
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Simple Function request, in Excel not VBA

    I'm still a little puzzled as to what you want. Your K formula basically says, "are any of the numbers in E:I found in L:Q?". That's as good of a way to do it as any. Your L formula just looks up the matching values from the A:I table for the J2 value. There's not a lot you can do to improve it, maybe:

    =INDEX($A$2:$K$102,MATCH(J2,A:A,0)-1,COLUMNS($L2:L2))

    The *1 was not needed, and the COLUMNS instead of the hardcoded 4 means you can drag it to the right. A VLOOKUP would also work:

    =VLOOKUP(J2,$A$2:$K$102,COLUMNS($L$2:L2)+3,FALSE)

    As far as the combined formula you attempted in post 5, as far as I can tell, you want to replicate the K formula, without using the L:Q formulas. In other words, given an ID from column J, do any of the numbers in E:I on the current line match any of the numbers on the line with the ID from J? If so, try:

    =IFERROR(SUM(COUNTIF(INDEX($D$2:$I$102,MATCH(J2,$A$2:$A$102,0),0),D2:I2))>0,FALSE)
    confirmed with Control+Shift+Enter.

    Or, using the same type of formula as you used before, try:

    =SUMPRODUCT(--(INDEX($D$2:$I$102,MATCH(J2,$A$2:$A$102,0),0)=D2:I2))>0

    Add IFERROR around it if you want to avoid the #N/A you'd get when J2 isn't found in column A.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  7. #7
    MrExcel MVP
    Join Date
    May 2011
    Posts
    3,699
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Simple Function request, in Excel not VBA

    Quote Originally Posted by hitbid View Post
    Here is a screen shot.
    The goal is to type any single code from column A into Column J, and get an answer in Column K. What it is doing is finding the values of the code's row that I entered in column J, and then populating in Columns L-Q.
    When I type any code into Column J, I have indexes in Columns L-Q that populate what is in the row of the ID I entered into column J.
    This "works" as is, but as an exercise in better formula writing, I am trying to condense into one formula in Column K, vs this mess.
    Column K example formula is: =SUMPRODUCT(--(D2:I2=L2:Q2))>0 . Very simple
    Column L example formula is: =INDEX($A$2:$K$102,MATCH(J2,A:A,0)*1-1,4) . Only the last digit in Column's M-Q change from this Column L formula.

    Hi!

    Try this in K2 and copy down

    =IFERROR(IF(LOOKUP(1/6,1/MMULT((D2:I2=D$2:I$102)*(A$2:A$102=J2),{1;1;1;1;1;1})),"Yes"),"No")


    A B C D E F G H I J K L
    1 ID B C D E F G H I Enter in ID Do ID's have a match?
    2 153 M 1 897 520 595 134 258 428 761 Yes
    3 545 F 2 642 752 174 625 761 107 599 No
    4 519 F 2 351 405 753 251 400 589 639 No
    5 765 F 2 536 640 486 775 543 620 111 No
    6 761 F 2 897 520 595 134 258 428 109 No
    7 686 M 1 498 444 594 771 451 582 781 No
    8 346 F 2 456 237 410 590 727 765 155 Yes
    9 343 F 2 550 515 751 885 834 887 518 No
    10 576 F 2 693 645 499 769 865 566 799 No
    11 429 F 2 436 488 617 476 346 462 220 No
    12 155 F 2 456 237 410 590 727 765 336 No
    13 594 F 2 279 426 301 497 719 232 181 No
    *** ***** ** ** ***** ***** ***** ***** ***** ***** ********* ******************* **


    Markmzz
    Last edited by markmzz; Apr 7th, 2018 at 05:29 PM.

  8. #8
    Board Regular
    Join Date
    Jan 2016
    Posts
    111
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Simple Function request, in Excel not VBA

    Amazing formulas, Eric and Mark. Thanks so much.

    =IFERROR(SUM(COUNTIF(INDEX($D$2:$I$102,MATCH(J2,$A$2:$A$102,0),0),D2:I2))>0,FALSE)
    confirmed with Control+Shift+Enter.

    This worked Eric. I'm going to evaluate this formula so I can really understand what is going on here. Good learning for me. This is exactly what I was trying to do. Eliminate the need to create separate formulas in Columns L:Q and just use one in K.

    =IFERROR(IF(LOOKUP(1/6,1/MMULT((D2:I2=D$2:I$102)*(A$2:A$102=J2),{1;1;1;1;1;1})),"Yes"),"No")
    This one worked as well, but did not match zeros, just the numbers greater than zero. I did not provide zeros in my screen shot, so makes sense.
    This formula is very different than what I've seen in the past. What are these two lines doing, or could you explain a little more about these?
    LOOKUP(1/6,1/MMULT
    {1;1;1;1;1;1}

    Thanks again. I enjoy these types of formulas.

  9. #9
    MrExcel MVP
    Join Date
    May 2011
    Posts
    3,699
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Simple Function request, in Excel not VBA

    Quote Originally Posted by hitbid View Post

    =IFERROR(IF(LOOKUP(1/6,1/MMULT((D2:I2=D$2:I$102)*(A$2:A$102=J2),{1;1;1;1;1;1})),"Yes"),"No")
    This one worked as well, but did not match zeros, just the numbers greater than zero. I did not provide zeros in my screen shot, so makes sense.
    Hi Hitbid!

    I'm sorry, but I didn't understand. Could you post a example?

    Markmzz

  10. #10
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,661
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Simple Function request, in Excel not VBA

    With 3 formulas to choose from, I got interested in which might be the "best". So I ran a few comparison and performance tests.

    First, my original formula with the COUNTIF worked fine.

    Next, my second formula with the SUMPRODUCT did NOT work if the numbers in the columns were in a different order on the two rows. For example, if the numbers on the first row were 1,2,3,4,5,6 and the numbers on the matching row are 7,8,9,10,11,1 it would not call that a match. I managed to update the formula to:

    =SUMPRODUCT(--(INDEX($D$2:$I$102,MATCH(J2,$A$2:$A$102,0),0)=TRANSPOSE(D2:I2)))>0
    with Control+Shift+Enter and it works fine now.

    Finally, I tried Mark's formula. That intrigued me since MMULT formulas are a lot of fun. However, I thought it might be slower since my formulas use MATCH to limit the comparisons to 1 row, while the MMULT looks at the entire table. It seems to suffer from the same problem as my second formula, where the order of the numbers matters. I couldn't figure out how to remediate it. Maybe the numbers will always be in the same position so it doesn't matter, or maybe this is the "did not match zero" problem in another guise.

    As far as performance, the COUNTIF and SUMPRODUCT formulas ran at similar speeds (COUNTIF a tad quicker), while the MMULT formula took between 2 and 10 times as long. However, with such a relatively small table, you'd never notice the difference.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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