Help with Match Index and MAX Formula

[brendongl]

	A		I
1	01/05/17		Apple
2	02/05/17		Orange
3	01/06/17		Apple

<tbody>
</tbody>

Hello. I have spent quite some time making a formula to display the date for the latest sale of some of my products. I have another topic about this but those formulas did not work, i believe because the max function only operates if you are using numbers? (it says on the microsoft support site "[FONT=&quot]If the arguments contain no numbers, MAX returns 0 (zero).")
[/FONT]
Column A is actually a formula giving them date as it is a vlookup function. Maybe that is why i have been failing making this formula??

 

[brendongl]

=LOOKUP(9.99999999999999E+307,SEARCH([@SKU],SalesRecords!I:I),SalesRecords!A:A)

I used the 2nd one.

That is the formula i made off your help.
Its causing mass slowdown as i put it in a table and formatted downwards around 2000rows.
Its caused my whole navigation of my sheets to be quite slow.

EDIT:
Tried changing it to $I$2:$I$9999 and $A$2:$A$9999 but there is still mass slowdown overall.
I am guessing that 9.99999999999999E+307 is just a large value in place to make sure it will search all dates? Can this be causing the CPU to calculate too much? I am running 64bit as well.
Your help in this matter is greatly appreciated and caused my headache to go away :D

 

[Aladin Akyurek]

=LOOKUP(9.99999999999999E+307,SEARCH([@SKU],SalesRecords!I:I),SalesRecords!A:A)

I used the 2nd one.

That is the formula i made off your help.
Its causing mass slowdown as i put it in a table and formatted downwards around 2000rows.
Its caused my whole navigation of my sheets to be quite slow.

Do you have any formula in column I of SalesRecords?

What is the value in [@SKU]?

 

[Aladin Akyurek]

The big number has nothing to do with the slowdown. Please try to answer the questions of post #12.

 

[brendongl]

Yes, there is a formula in Both column I and Column A in SalesRecord which returns the date and sku from another sheet.


What i meant was I autofilled the formula down my table of 2000 or so rows and now everytime I try to edit something on the whole sheet, it takes 5-10 seconds to load.
I just tested it with one row with the formula, and it is still causing massive delays.

 

[Aladin Akyurek]

Yes, there is a formula in Both column I and Column A in SalesRecord which returns the date and sku from another sheet.


What i meant was I autofilled the formula down my table of 2000 or so rows and now everytime I try to edit something on the whole sheet, it takes 5-10 seconds to load.
I just tested it with one row with the formula, and it is still causing massive delays.

What are the formulas that you have in A and in I?

What is the first value [@SKU] for which you enter the formula?

 

[brendongl]

Column A and I is a vlookup of a table in another sheet. [=VLOOKUP(B675,INDIRECT("SoldList"),24,0)]
[@SKU] Is a value not a formula. The first value in my table is

AC-200L-GD

<colgroup><col></colgroup><tbody>
</tbody>

But i dont see how that is relevant.

Even after deleting the formula, my sheet has been acting abit slower. I reset my PC, but now im turning calculation options to manual for now. This may be a workaround but i think i have to clean up my sheet to make it faster.

 

[Aladin Akyurek]

1. Is

=VLOOKUP(B675,INDIRECT("SoldList"),24,0)

a formula of A or I?

2. What does this return: text or number?

3. Why do you need the volatile (notoriously inefficient) INDIRECT<strike></strike> here, i.e. is just SlodList not sufficient?

4. Are we trying to retrieve the last date associated with values like AC-200L-GD<strike></strike>?

 

[brendongl]

1. Column A and I have very similiar formulas. [=VLOOKUP(B679,INDIRECT("SoldList"),24,0] and [=VLOOKUP(B679,INDIRECT("SoldList"),32,0)].
That is row b679. Column B is a value not a function.

2) In column A, it returns a number which is formatted to a date. The data it is looking up is formatted like this [8/05/2017]
In column I, it returns text. The data it is looking up is a text value.

3) I need INDIRECT because i regularly delete the tables and refresh them with a new sheet which i download with new data.

4) Yes we are trying to retrive the last date associated with the Values like AC-200L-GD.

 

[Aladin Akyurek]

1. Column A and I have very similiar formulas. [=VLOOKUP(B679,INDIRECT("SoldList"),24,0] and [=VLOOKUP(B679,INDIRECT("SoldList"),32,0)].
That is row b679. Column B is a value not a function.

2) In column A, it returns a number which is formatted to a date. The data it is looking up is formatted like this [8/05/2017]
In column I, it returns text. The data it is looking up is a text value.

3) I need INDIRECT because i regularly delete the tables and refresh them with a new sheet which i download with new data.

4) Yes we are trying to retrive the last date associated with the Values like AC-200L-GD.

Thanks. Care to post the table (just first 5 rows including the headers) in which you need the LOOKUP formula?

 

[brendongl]

So there are two sheets. SalesRecord and ItemList.

SalesRecord as we know, has the Date and SKU of the cells which we need to lookup.
The formula ideally needs to be on the seperate sheet ITEMLIST.

ItemList has over 20 columns so its abit tedious to post, why do we need that information to create the lookup??
All we need is Column A, which is @SKU, which is what we are using to lookup on SalesRecord.

But if your talking about the SalesRecord...

DateOfSale	SaleRecord	Email	Name	Ph	ID	OID	SKU
9/05/2017 <colgroup><col width="80" style="width:60pt"> </colgroup><tbody> </tbody>	3445 <colgroup><col></colgroup><tbody> </tbody>						MDETECTOR-GC1010 <colgroup><col></colgroup><tbody> </tbody>
9/05/2017 <colgroup><col width="80" style="width:60pt"> </colgroup><tbody> </tbody>							BIN-WALL-30 <colgroup><col width="136" style="width:102pt"> </colgroup><tbody> </tbody>
9/05/2017 <colgroup><col width="80" style="width:60pt"> </colgroup><tbody> </tbody>							PET-BIRDCAGE-A102-BK <colgroup><col></colgroup><tbody> </tbody>
9/05/2017 <colgroup><col width="80" style="width:60pt"> </colgroup><tbody> </tbody>							PO-CL-P1805 <colgroup><col width="136" style="width:102pt"> </colgroup><tbody> </tbody>

<tbody>
</tbody>

Im confused as to how this helps you help me? Of course those are the values, and not the formulas, as i have stated what formulas are used in there already.
My sheet is at 1mb and its running so slowly. Working with calculations on manual is not ideal.
I dont get why even when this sheet is open and idle, my other open workbooks take there sweet time to calculate simple things like copy and paste...