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
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.
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!
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
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.
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.
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
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.
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.
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
Like this thread? Share it with others