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

Lookup value when column count changes

T

Thomas Kuriakose

Active Member
Respected Sirs,

Kindly find attached a data where we need to lookup up row value and get the respective value from the Column Total header.

I would like to know how to get the correct lookup value when the number of columns before header column total keeps changing with every data. The number of columns could be from 1 to 120.

Thank you very much,

with regards,
thomas
 

Attachments

Thomas Kuriakose said:
Respected Sir,

On small issue, we have one value in data tab shifted to column B6, because the format requires this.

Kindly let me know how to get this value.

Thank you very much,

with regards,
thomas
Click to expand...

1] Your attachment file formula used :

=VLOOKUP(A2,Data!A$5:M$15,MATCH("ColumnTotal",Data!$1:$1,0),FALSE)

Please note that, this formula Lookup Table (A$5:M$15) will not expand if the Column Total changing and moving right.

2] Your post #5 problem, using the following revised formula instead.

=INDEX(Data!$5:$15,IFERROR(MATCH(A2,Data!$A$5:$A$15,0),MATCH(A2,Data!$B$5:$B$15,0)),MATCH("zzz",Data!$1:$1))

Regards
Bosco
 
Respected Sir,

One question, I am not able to get the result in the actual data workbook. The result is zero.

I am not able to understand where I am going wrong.

Kindly help,

with regards,
thomas
 
Respected Sir,

Apologies, missed to input the formula -

=INDEX('Cost Sheet Details '!$34:$53,MATCH(Cockpit!D50,'Cost Sheet Details '!$B$34:$B$53,0),MATCH("zzz",'Cost Sheet Details '!$7:$7))

Thanks,

with regards,
thomas
 
Respected Sir,

I simulated the problem and found the cause, there are two columns after the Column Total and in my test sheet this was not there. Apologies for not providing this information.

Kindly find attached the file with zero result.

Thanks,

with regards,
thomas
 

Attachments

This attachment is difference from the post #1 file, in row no.1 with adding "Budget" and "Difference" at the end.

The attachment formula is design for lookup the last text in row no.1 as the criteria, and unable to work in this revised example.

Thus, in order to meet your revised layout, please use one of the following revised formula.

=INDEX(Data!$5:$15,IFERROR(MATCH(A2,Data!$A$5:$A$15,0),MATCH(A2,Data!$B$5:$B$15,0)),MATCH("ColumnTotal",Data!$1:$1,0))

or,

=OFFSET(Data!A$4,IFERROR(MATCH(A2,Data!$A$5:$A$15,0),MATCH(A2,Data!$B$5:$B$15,0)),MATCH("ColumnTotal",Data!$1:$1,0)-1)

Regards
Bosco
 

Attachments

Last edited:
Respected Sir,

Apologies once again for the incomplete requirement.

Thank you very much for the revised formula provided.

You are the formula master.

with regards,
thomas
 
You must log in or register to reply here.
Back
Top