Simple Function request, in Excel not VBA

hitbid

Board Regular
Joined
Jan 21, 2016
Messages
114
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.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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
 
Upvote 0
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.
 
Upvote 0
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.

11ryg6d.jpg
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.

11ryg6d.jpg

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")


ABCDEFGHIJKL
1IDBCDEFGHIEnter in IDDo ID's have a match?
2153M1897520595134258428761Yes
3545F2642752174625761107599No
4519F2351405753251400589639No
5765F2536640486775543620111No
6761F2897520595134258428109No
7686M1498444594771451582781No
8346F2456237410590727765155Yes
9343F2550515751885834887518No
10576F2693645499769865566799No
11429F2436488617476346462220No
12155F2456237410590727765336No
13594F2279426301497719232181No
************************************************************************

<tbody>
</tbody>


Markmzz
 
Last edited:
Upvote 0
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.
 
Upvote 0

=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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top