My formula is not displaying the result.

[jjk1]

My formula is not displaying the result. It seems like it is identifying the result, but not displaying it in the cell where this formula resides.

=IFERROR(INDEX($J1, MATCH(MIN(IF(ISNUMBER($D:$D), MATCH($D:$D, $D:$D, 0))), MATCH($D:$D, $D:$D, 0), 0)), "")

What its suppose to do:
Search column D.
This first time a value appears in column D, identify the number that appears in column J of the same row, and display it in column K of the same row where the formula is.
In column D, any instances where the value appears multiple times, it is only the first occurrence that should be displayed.

(this formula searches "numbers" in column D. Once I can get this to work, then i need one that searches through "text" in column D) and works the same way.

Thank you

 

[AhoyNC]

You need to post an actual example of what you are trying to do. I'm not sure what your above formula is doing.
What is the result from your above formula and how is it used to get the result in column K?

 

[jjk1]

Here is the example related to my question on how to apply the formula you wrote to my formula in cell J14. I want to put the formula in K14.

 

[Fluff]

@jjk1 can you please post your actual data rather than an image. Members do not want to have to type all that out themselves.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[jjk1]

As requested for above question....

Test1Mr.Excel.xlsx
	A	B	C	D	E	F	G	H	I	J
11
12			SMOOTTY
13	Data Source 1	Data Source 2	Text	Reference						Identity
14	LZA00000001EF1239876 83D 21Q000000000000257943-000000000000257943-000000000150696388+000000000000000000+000000000150954331+000000000002667584-000000000150954331+000000000153363972+000000000002409397+000000000000000244+000000000000257943- 20220930X	LMA00000001EF12398761ABC999997 ABC999997US 83D21QCABC2022093020220930000000000257943000+000000000257943000+000000000000000000+000000000000000000+000000000000000000+000000000000000000+000000000000000000+000000000000000000+000000000000000000+000000000000000000+000000000000000000+000000000000000000+000000000000000000+000000000000000000+000000000000000000+000000000000000000+000000000000000000+000000000000000000+000000000000000000+ABC99999710 000000000000000000+00000000000000000000000000000000000000000000SMOOTTY 01305210750001ABCDEFGHIJK LMNOPQRS NABC000000000002579430+0000000000+000000010000000000+000000000002579430+ABC X	SMOOTTY	EF1239876						26673.4
15	LZB00000001EF1239876 83D 21Q000000000000257943-000000000000257943-000000000150696388+000000000000000000+00000000000000000000000000+000000000000000000+000000000000000000+000000000076640416+000000000000000000+ 000000000000000000+ 20220930X
16
Sheet4

Cell Formulas
Range	Formula
C14	C14	=IF(AND(MID(B14,3,1)="a",(TRIM(MID(B14,514,10))=$C$12)),(MID(B14,514,10)),"")
D14	D14	=IF(AND(MID(B14,3,1)="a",(TRIM(MID(B14,514,10))=$C$12)),(MID(B14,12,9)),"")
J14	J14	=IFERROR(IF(IF(MID(A15,3,1)="b",IF(AND(MID(A15,47,1)="-",(MID(A15,29,18)/100)<>0),"+",IF(AND(MID(A15,47,1)="+",(MID(A15,29,18)/100)<>0),"-","")),"")="+",IF(MID(A14,3,1)="a",MID(A14,181,18)/100,"")+IF(MID(A15,3,1)="b",MID(A15,29,18)/100,"")*1,IF(MID(A14,3,1)="a",MID(A14,181,18)/100,"")+IF(MID(A15,3,1)="b",MID(A15,29,18)/100,"")*-1),"")

 

[jjk1]

You need to post an actual example of what you are trying to do. I'm not sure what your above formula is doing.
What is the result from your above formula and how is it used to get the result in column K?

You need to post an actual example of what you are trying to do. I'm not sure what your above formula is doing.
What is the result from your above formula and how is it used to get the result in column K?

Hi AhoyNC,

Just wanted to let you know that earlier this am I posted the minisheet from XL2BB with the actual data for my latest question. Thank you.

 

[AhoyNC]

I'm assuming your data starts in row 14 then the formula below goes down to row 50000. If you have more than 50,000 rows of data change ranges as needed.
Assuming you are trying to match the numbers in column J with the reference in column D (which has your formula).
Then try this formula in column K.

=IF(D14=D13,"",VLOOKUP(D14,$D$14:$J$50000,7,0))

 

[jjk1]

I'm assuming your data starts in row 14 then the formula below goes down to row 50000. If you have more than 50,000 rows of data change ranges as needed.
Assuming you are trying to match the numbers in column J with the reference in column D (which has your formula).
Then try this formula in column K.

=IF(D14=D13,"",VLOOKUP(D14,$D$14:$J$50000,7,0))

Thank you.
A couple issues. Your new formula pulls data from the results in columns C thru J. There are reasons I can't do that, the data has to come from the original source, i.e. the data strings in column A.
Also the functionality gained in your earlier formula =IF(COUNTIF($A$14:$A14,A14)=1,INDEX($B$14:$B$41,MATCH($B14,$B$14:$B14,0)),"") was partially lost in the formula above, it did not work consistently with my data.

However, since my formula worked in extracting the data from the strings in the nuanced manner I wanted, I was hoping that you could you could show me how to apply your formula =IF(COUNTIF($A$14:$A14,A14)=1,INDEX($B$14:$B$41,MATCH($B14,$B$14:$B14,0)),"") to the one I made so I could gain the functionality of your formula and apply it to mine.

Thank you,

 

[AhoyNC]

See if this simplified example is something you can adjust to your data.

Book1
	A	B	C	D				H	I	J	K
12
13	Data 1	Data 2		Reference						Identity
14	kflgkfjf			flgk						12	12
15	kflgkfjf			flgk						13
16	kflgkfjf			flgk						14
17	fgfmhkfg			gfmh						15	15
18	fgkvkfl			gkvk						16	16
19	fgkvkfl			gkvk						17
20	fgkvkfl			gkvk						18
21	fgkvkfl			gkvk						19
Sheet3

Cell Formulas
Range	Formula
D14:D21	D14	=MID(A14,2,4)
K14:K21	K14	=IF(A14=A13,"",INDEX($J$14:$J$21,MATCH(MID(A14,2,4),MID($A$14:$A$21,2,4),0)))