# VLOOKUP Book - Last Occurance formula

#### dimzum133

##### New Member
I have recently bought the book VLookup book by chandoo and i have some problem on how i can get the last or final occurance, at the moment i can set the
occurance by setting the numbers but how do i get the last occurance?

This is the current formula where the number 2 at the end will show the 2nd occurance, but i don't know how to get the last occurance

=INDEX(\$E\$5:\$E\$17, SMALL(IF (\$C\$5:\$C\$17="JOHN", ROW(\$C\$5:\$C\$17)-ROW(\$C4)), 2))

Also is there a way to alter the formula to show:
1. the occurance when its value matches a condition

many thanks

#### AliGW

##### Active Member
Try this:

=LOOKUP(2,1/(\$C\$5:\$C\$17=”JOHN”), \$E\$5:\$E\$17)

#### GraH - Guido

##### Well-Known Member
When SMALL gives first, LARGE might give last?

#### Peter Bartholomew

##### Well-Known Member
If you are developing for Microsoft 365, many of the more 'interesting' methods from the past can be allowed to slip into obsolescence.

For example
=ROW(\$C\$5:\$C\$17)-ROW(\$C4)
can now be replaced by
= SEQUENCE(13)
It is now rare to need anything but XLOOKUP for looking up data (maybe FILTER for multiple return values). In the example you introduce
= XLOOKUP( name, IF(valueList>10, nameList), valueList, , ,1)
returns the value corresponding to the first occurrence of 'name' but
= XLOOKUP( name, IF(valueList>10, nameList), valueList, , , -1)
returns the last.
= INDEX( FILTER(valueList, nameList=name), 5 )
will return the 5 match from a sequence.

To match a condition on the value returned, one can either filter by the value and then lookup the name or the reverse, e.g.
= XLOOKUP( name, IF(valueList>10, nameList), valueList, , , -1)
Overall the tools are so much better with modern dynamic arrays.

#### dimzum133

##### New Member
many thanks for the help, at the moment i am using LARGE to get the last occurance

also i am having trouble with the formula to make it the cell blank if the price 2 has been filled

enclosed is the sample data i want to do, the highlighted yellow is showing those cells which price 2 has not yet been filled based on the last dates

#### Attachments

• 11.9 KB Views: 4
Last edited:

#### AliGW

##### Active Member
Try this in B3 copied down:

=LOOKUP(2,1/((\$A\$17:\$A\$66=MAXIFS(\$A\$17:\$A\$66,\$B\$17:\$B\$66,A3))*(\$B\$17:\$B\$66=A3)),\$C\$17:\$C\$66)

#### dimzum133

##### New Member
Hi Ali, thanks for the formula it worked perfectly

If i want to make a criteria to only run the formula only when the cell from the data list is empty, how do it make it?

assuming i will be adding another column from the data list and will ask the formula to check if the cell is empty or not

I tried using =IF(ISBLANK) function and copied down the LOOKUP formula to check if the cell is blank but Excel is showing error "too many arguments

=IF(ISBLANK( LOOKUP(2,1/((\$A\$17:\$A\$66=MAXIFS(\$A\$17:\$A\$66,\$B\$17:\$B\$66,A3))*(\$B\$17:\$B\$66=A3)),\$D\$17:\$D\$66),
LOOKUP(2,1/((\$A\$17:\$A\$66=MAXIFS(\$A\$17:\$A\$66,\$B\$17:\$B\$66,A3))*(\$B\$17:\$B\$66=A3)),\$C\$17:\$C\$66), " ")

many thanks again

Last edited:

#### AliGW

##### Active Member
Looks like a closing bracket is missing:

=IF(ISBLANK( LOOKUP(2,1/((\$A\$17:\$A\$66=MAXIFS(\$A\$17:\$A\$66,\$B\$17:\$B\$66,A3))*(\$B\$17:\$B\$66=A3)),\$D\$17:\$D\$66)),
LOOKUP(2,1/((\$A\$17:\$A\$66=MAXIFS(\$A\$17:\$A\$66,\$B\$17:\$B\$66,A3))*(\$B\$17:\$B\$66=A3)),\$C\$17:\$C\$66),"")

#### dimzum133

##### New Member
Thanks

After i tried the formula it is showing " " to all the cells even if some of the cells in D column are not blank

I tried the IF SUMPRODUCT but it is showing all " " (blanks) also

=IF(SUMPRODUCT(--(C42:C47="")),"Blank","All Filled")

any other way i can change the formula?

#### AliGW

##### Active Member
Try this:

=IF(LOOKUP(2,1/((\$A\$17:\$A\$66=MAXIFS(\$A\$17:\$A\$66,\$B\$17:\$B\$66,A3))*(\$B\$17:\$B\$66=A3)),\$D\$17:\$D\$66)="",
LOOKUP(2,1/((\$A\$17:\$A\$66=MAXIFS(\$A\$17:\$A\$66,\$B\$17:\$B\$66,A3))*(\$B\$17:\$B\$66=A3)),\$C\$17:\$C\$66),"")

Failing this, attach the workbook.

#### dimzum133

##### New Member
Thanks for the help again Ali, i tried the last formula but it results in all the cells becoming all blank " "

I wanted to search the column F and only run the formula if comparing with the main list to only run the formula when the cells on column F are blank, otherwise will result in " "

many thanks again

#### Attachments

• 70.9 KB Views: 3

#### AliGW

##### Active Member
Please add in column F at the top the results you want to see for the first 10 rows (manually entered) and post again. I am not following your rather odd description, sorry.

#### dimzum133

##### New Member
hi Ali, again thanks for your help

the Main list which is from C5 to E37 is where it will show the last price #1 match getting the data from A41 to F1047

The formula i have now will get the last match from A41 to F1047 based on the codes, i want to change it so that if price#2 from the F column has input then the main list will show BLANK

if price#2 is blank then will show the result in the main list

this way because the data list will become longer as time goes by, i want to just simply show me the main list which price#2 has not been filled yet so i can fill them later on and not miss it

Hope it makes sense

Last edited:

#### AliGW

##### Active Member
This?

=IF(LOOKUP(2,1/((\$A\$41:\$A\$9999=MAXIFS(\$A\$41:\$A\$9999, \$B\$41:\$B\$9999, \$D5))*(\$B\$41:\$B\$9999=\$D5)),\$F\$41:\$F\$9999)="",LOOKUP(2,1/((\$A\$41:\$A\$9999=MAXIFS(\$A\$41:\$A\$9999, \$B\$41:\$B\$9999, \$D5))*(\$B\$41:\$B\$9999=\$D5)),\$C\$41:\$C\$9999),LOOKUP(2,1/((\$A\$41:\$A\$9999=MAXIFS(\$A\$41:\$A\$9999, \$B\$41:\$B\$9999, \$D5))*(\$B\$41:\$B\$9999=\$D5)),\$F\$41:\$F\$9999))

#### Peter Bartholomew

##### Well-Known Member
I had a look at the workbook in #11 to see what was going on. Because I find it difficult to decode direct references and still extract meaning, I converted the reference data to a table. This showed the formula for the date to be
= LOOKUP(2, 1/((Data[DATE]=MAXIFS(Data[DATE], Data[.CODE], @CODE)) * (Data[.CODE]=@CODE) ), Data[DATE])
[where @CODE is simply a dynamic array notation for the intersection of the formula row and the range CODE]

As far as I can see this gives the same result as
= MAXIFS(Data[DATE], Data[.CODE], @CODE)
the use of which also simplifies the Price formula
= LOOKUP(2, 1/( (Data[DATE]=@DATE) * (Data[.CODE]=@CODE) ), Data[PRICE '#1])

More has happened since that I haven't looked at.