# My formula is not displaying the result.



## jjk1 (Jan 4, 2023)

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


----------



## ExceLoki (Jan 4, 2023)

which version of excel are you using? this helps when trying to find a solution. you can update in your profile.


----------



## AhoyNC (Jan 4, 2023)

Your INDEX syntax is wrong.
If you are just looking for the first number in column D try changing your INDEX formula to:
INDEX(J:J,MATCH(TRUE,ISNUMBER(D:D),0)

Also, it is not the best idea to reference whole columns. If your data won't go below say row 10000 and you have headers in row one then reduce your range to J1:J10000.

As pointed out by ExceLoki it helps if we know what version of Excel you are using.


----------



## jjk1 (Jan 4, 2023)

I updated my prole.  Its excel 2019 in windows.

I tried the formula y recommended and it does not work.  It returns #N/A.

If I was not clear, column D and each cell will have a number.  The column will have a variety of numbers.  So for the if the number is the first occurrence within column D of that number, then I want it to display the value in the same row from column J.

Please let me know if further clarification is needed.  thank you.


----------



## jjk1 (Jan 4, 2023)

ExceLoki said:


> which version of excel are you using? this helps when trying to find a solution. you can update in your profile.


excel 2019 windows version.


----------



## jjk1 (Jan 4, 2023)

AhoyNC said:


> Your INDEX syntax is wrong.
> If you are just looking for the first number in column D try changing your INDEX formula to:
> INDEX(J:J,MATCH(TRUE,ISNUMBER(D:D),0)
> 
> ...


I tried your formula but it did not work. it returns #N/A


----------



## AhoyNC (Jan 4, 2023)

It would help if you would post a small sample and the expected results.
Are you looking for something like this?
Book2DEJK261010322020410303056401064550507160608457050968010Sheet1Cell FormulasRangeFormulaK2:K9K2=INDEX($J$2:$J$9,MATCH($D2,$D$2:$D$9,0))


----------



## jjk1 (Jan 4, 2023)

In my sample image the formula should produce the sample results as they appear in column K.
In your example 6 appears 3 times, but it should only produce a result the first time it appears in column D.  Also, 45 appears twice, it should only produce a result the first time i appears in column D.


----------



## AhoyNC (Jan 4, 2023)

Try:
Book2ABCDEJK10Random11Ref NumberFormula121314EF1239876$3.00$3.0015EF1239876$4.00 16EF1239876$5.00 17EF1239833$6.00$6.0018EF1239833$7.00 19EF1239833$8.00 20EF1239833$9.00 21EF1239890$10.00$10.0022EF1239897$11.00$11.0023EF1239897$12.00 24EF1239897$13.00 25EF1239897$14.00 26EF1239897$15.00 27EF1239904$16.00$16.0028EF1239911$17.00$17.0029EF1239911$18.00 30EF1239911$19.00 31EF1239918$20.00$20.0032EF1239925$21.00$21.0033EF1239925$22.00 34EF1239925$23.00 35EF1239925$24.00 36EF1239925$25.00 37EF1239925$26.00 38EF1239332$27.00$27.0039EF1239939$28.00$28.0040EF1239946$29.00$29.0041EF1239953$30.00$30.00Sheet2Cell FormulasRangeFormulaK14:K41K14=IF(COUNTIF($A$14:$A14,A14)=1,INDEX($B$14:$B$41,MATCH($B14,$B$14:$B14,0)),"")


----------



## jjk1 (Jan 4, 2023)

Thank you it works perfect!!
Now I have add a layer of complexity.  I need to use our formula to pull out the result from within a text string in my formula.  The result would appear in column "K" for each row as you did above.

The formula below works for what I need, however I need the conditions that are in the formula you wrote to apply the below formula:

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

Thank you!


----------



## jjk1 (Jan 4, 2023)

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 (Jan 4, 2023)

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 (Jan 5, 2023)

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 (Jan 5, 2023)

@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 (Jan 5, 2023)

As requested for above question....

Test1Mr.Excel.xlsxABCDEFGHIJ1112SMOOTTY13Data Source 1Data Source 2TextReferenceIdentity14LZA00000001EF1239876 83D 21Q000000000000257943-000000000000257943-000000000150696388+000000000000000000+000000000150954331+000000000002667584-000000000150954331+000000000153363972+000000000002409397+000000000000000244+000000000000257943-    20220930XLMA00000001EF12398761ABC999997    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              XSMOOTTY   EF123987626673.415LZB00000001EF1239876 83D 21Q000000000000257943-000000000000257943-000000000150696388+000000000000000000+00000000000000000000000000+000000000000000000+000000000000000000+000000000076640416+000000000000000000+    000000000000000000+           20220930X 16Sheet4Cell FormulasRangeFormulaC14C14=IF(AND(MID(B14,3,1)="a",(TRIM(MID(B14,514,10))=$C$12)),(MID(B14,514,10)),"")D14D14=IF(AND(MID(B14,3,1)="a",(TRIM(MID(B14,514,10))=$C$12)),(MID(B14,12,9)),"")J14J14=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 (Jan 5, 2023)

AhoyNC said:


> 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?





AhoyNC said:


> 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 (Jan 5, 2023)

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 (Jan 5, 2023)

AhoyNC said:


> 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 (Jan 5, 2023)

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

Book1ABCDHIJK1213Data 1Data 2ReferenceIdentity14kflgkfjfflgk121215kflgkfjfflgk13 16kflgkfjfflgk14 17fgfmhkfggfmh151518fgkvkflgkvk161619fgkvkflgkvk17 20fgkvkflgkvk18 21fgkvkflgkvk19 Sheet3Cell FormulasRangeFormulaD14:D21D14=MID(A14,2,4)K14:K21K14=IF(A14=A13,"",INDEX($J$14:$J$21,MATCH(MID(A14,2,4),MID($A$14:$A$21,2,4),0)))


----------

