SOLVED
Home

vlookup using concatenate function and helper column

%3CLINGO-SUB%20id%3D%22lingo-sub-335093%22%20slang%3D%22en-US%22%3Evlookup%20using%20concatenate%20function%20and%20helper%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-335093%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20-%20I've%20set%20up%20a%20table%20to%20record%20project%20costs.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20save%20time%2C%20there%20are%20some%20standard%20unit%20costs%20that%20I%20want%20to%20pull%20through%20from%20another%20tab%20in%20the%20same%20worksheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%20Consultant%20'Joe%20Bloggs'%20has%20a%20day%20rate%20of%20%C2%A3500.%20So%20if%20the%20selections%20from%20the%20drop%20down%20lists%20in%20two%20of%20the%20columns%20on%20the%20costing%20sheet%20match%20'Joe%20Bloggs'%20and%20'Consulting%20Fees'%20I%20want%20the%20unit%20cost%20to%20automatically%20populate%20as%20%C2%A3500.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20found%20a%20solution%20that%20described%20using%20the%20CONCATENATE%20function%20to%20merge%20two%20values%20into%20a%20'helper'%20column%20in%20my%20look%20up%20table.%20It%20partially%20worked%2C%20but%20not%20all%20the%20values%20were%20coming%20through%20correctly.%20I've%20tinkered%20with%20it%20and%20now%20none%20of%20the%20values%20are%20coming%20through%20at%20all!!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anyone%20spot%20where%20I%20may%20have%20gone%20wrong%20on%20the%20attached%20file%3F%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E(NB%3A%20I%20did%20try%20this%20using%20IF%20and%20AND%20functions%20which%20does%20work%2C%20but%20the%20number%20of%20variables%20became%20too%20large)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-335093%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Econcatenate%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Evlookup%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-338427%22%20slang%3D%22en-US%22%3ERe%3A%20vlookup%20using%20concatenate%20function%20and%20helper%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-338427%22%20slang%3D%22en-US%22%3E%3CP%3EThat's%20great%20-%20thank%20you%2C%20I've%20never%20seen%20that%20one%20before%20%3A-)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-335143%22%20slang%3D%22en-US%22%3ERe%3A%20vlookup%20using%20concatenate%20function%20and%20helper%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-335143%22%20slang%3D%22en-US%22%3E%3CP%3EYou're%20welcome!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnyway%2C%20there%20is%20a%20powerful%20alternative%20to%20VLOOKUP%20called%20(INDEX%20%26amp%3B%20MATCH).%3C%2FP%3E%3CP%3EBy%20using%20this%20alternative%2C%20you%20don't%20have%20to%20use%20helper%20columns.%3C%2FP%3E%3CPRE%3E%3DINDEX('drop%20downs'!%24A%242%3A%24A%2453%2CINDEX(MATCH(B6%26amp%3BC6%2C'drop%20downs'!%24B%242%3A%24B%2453%26amp%3B'drop%20downs'!%24C%242%3A%24C%2453%2C0)%2C))%3C%2FPRE%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F72852iC4FC158E3DCD39E9%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22INDEX%20%26amp%3B%20MATCH.png%22%20title%3D%22INDEX%20%26amp%3B%20MATCH.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3EHaytham%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-335138%22%20slang%3D%22en-US%22%3ERe%3A%20vlookup%20using%20concatenate%20function%20and%20helper%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-335138%22%20slang%3D%22en-US%22%3E%3CP%3EThat's%20perfect%20-%20thanks%20so%20much%20for%20the%20quick%20and%20helpful%20reply!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-335128%22%20slang%3D%22en-US%22%3ERe%3A%20vlookup%20using%20concatenate%20function%20and%20helper%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-335128%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Angela%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat's%20because%20you%20use%20VLOOKUP%20in%20the%20approximate%20match%20mode.%3C%2FP%3E%3CP%3ESo%20please%20fix%20it%20as%20follows%3A%3C%2FP%3E%3CPRE%3E%3DVLOOKUP((CONCATENATE(B6%2CC6))%2C'drop%20downs'!%24A%241%3A%24B%2423%2C2%2C0)%3C%2FPRE%3E%3CP%3EBy%20setting%20the%20last%20argument%20to%200%20or%20FALSE%2C%20you're%20now%20in%20the%20Exact%20match%20mode.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Angela McGhin
Contributor

Hi - I've set up a table to record project costs.

 

To save time, there are some standard unit costs that I want to pull through from another tab in the same worksheet.

 

For example Consultant 'Joe Bloggs' has a day rate of £500. So if the selections from the drop down lists in two of the columns on the costing sheet match 'Joe Bloggs' and 'Consulting Fees' I want the unit cost to automatically populate as £500.

 

I found a solution that described using the CONCATENATE function to merge two values into a 'helper' column in my look up table. It partially worked, but not all the values were coming through correctly. I've tinkered with it and now none of the values are coming through at all!!

 

Can anyone spot where I may have gone wrong on the attached file??

 

(NB: I did try this using IF and AND functions which does work, but the number of variables became too large)

 

Thank you 

4 Replies
Solution

Hi Angela,

 

That's because you use VLOOKUP in the approximate match mode.

So please fix it as follows:

=VLOOKUP((CONCATENATE(B6,C6)),'drop downs'!$A$1:$B$23,2,0)

By setting the last argument to 0 or FALSE, you're now in the Exact match mode.

 

Hope that helps

 

That's perfect - thanks so much for the quick and helpful reply!

You're welcome!

 

Anyway, there is a powerful alternative to VLOOKUP called (INDEX & MATCH).

By using this alternative, you don't have to use helper columns.

=INDEX('drop downs'!$A$2:$A$53,INDEX(MATCH(B6&C6,'drop downs'!$B$2:$B$53&'drop downs'!$C$2:$C$53,0),))

INDEX & MATCH.png

 

 

Regards,

Haytham

That's great - thank you, I've never seen that one before :-)

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies