VLookup and Left/Right Extraction?

[2709236]

This may be a dumb question, but I want to use the vlookup function to help return the value in column 2, IF the reference cell matches the left 4 characters in column 1.

Oh, and also, is there a way to add together the returned values if it happens that more than one entry is valid?

All of this in one formula. I'm thinking it's not really feasible. Anyway, here's a sample of data


Column A Column B Column C
Red Toys - Wk27 7 Yes
Blue Toys - Wk32 5 No
Purple Toys - Wk27 3 Maybe

I want to 'lookup' Wk27, and return the value of 10.

Thank you!

[jasoncw]

Try this formula:

=SUMPRODUCT((RIGHT(A2:A100,4)="Wk27")*(B2:B100))

HTH

Jason

[2709236]

That worked! I REALLY need to use that SUMPRODUCT function better. Very handy. Thanks Jason!

[jasoncw]

No problem. Glad it worked for you.

[2709236]

This formula worked in the cell I inputted it to, but I'm having trouble copying and editing it to suit my other rows. In particular, I have the formula referencing other sheets in my workbook, and each time I copy this formula to a new row, I need to reference a new worksheet. I'm getting a #VALUE! error when I manually change the old referenced sheet to the new sheet I need. Any ideas why this is happening?

[Richard Schollar]

Please post the formula that is giving you this problem.

Richard

[2709236]

=SUMPRODUCT((RIGHT(RDW0100!$A$15:$A$69,4)=H$42)*(RDW0100!$B$15:$B$69))

I want to be able to change RDW0100 to RDW0098, RDW0112 - whichever the worksheet may be. Each worksheet represents a project, and I'm trying to summarize all projects' labour-hours by week on a summary page.

[Richard Schollar]

Nothing wrong with the formula (that I can see) that should result in a #VALUE! result so do you have any #VALUE!s in the referenced cell ranges in the source sheets?

[2709236]

Oh good idea! But, unfortunately no.
I think I figured out why I'm getting the error. The formula works great until I run into a text (title) in column B, and then it has a fit. Does that make sense?