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

problem with match plus lookup

T

tomas

Active Member
Hello. So I wanted to accomplish in a selected range to find last cell with different value than 0 a match should find the relative position of this cell in range.

Strangely enough formula in A1 gives me position 4 , even though it should have been . when I erase content from fourth cell result becomes correct.

PS1: I evaluated formula and everything seems to be ok.

PS2: first I did formula only for range c1:g1 and worked fine, then I tested formula extending range and got screwed by excel
 

Attachments

@tomas

You should change your formula to:

=MATCH(LOOKUP(999999999999999000,COLUMN(C1:H1)/(C1:H1<>"")),1/(C1:H1<>""))

But this is an array version.

Regards,
 
Hi Tomas ,

The issue is that the LOOKUP function is returning 1 , which is the maximum value possible within the MATCH function. This is what is leading to unpredictable results.

Consider these two cases :

1. =MATCH(1 , 1/(C1:I1<>""))

Here , try this formula with the following situations :

Enter any value in all of the cells C1 through I1 ; now start clearing the cells H1 , G1 , F1 , E1 , D1 and C1 one by one. The above formula will return the following results : 5 , 4 , 7 , 7 , 7 , 7

Thus , the output in the last 4 cases is 7 , which is the maximum possible.

2. =MATCH(2 , 1/(C1:I1<>""))

Try this formula with the same situations as before :

Enter any value in all of the cells C1 through I1 ; now start clearing the cells H1 , G1 , F1 , E1 , D1 and C1 one by one. The above formula will return the following results : 7 , 7 , 7 , 7 , 7 , 7

Thus , the output in all the cases is 7 , which is the maximum possible. Thus , this formula behaves the way you would expect it to.

The LOOKUP function is the culprit , for returning 1 which is a possible value within the array of values ; if you use a value for the MATCH function which is greater than any possible value within the array , that is when it will behave correctly.

Narayan
 
You must log in or register to reply here.
Back
Top