• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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
 

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

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

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.
 
Top