Vlookup Concatenated Value as a Variable

[beef4dinner]

Hi Guys,

I'm trying to code something that would be a big help to me. Effectively I need to concatenate two columns in Sheet1, and then vlookup each concatenated value from Sheet1 in another concatenated column in Sheet2 and return the value from the next column

Now that is quite straight forward, but I am new to coding, and I was wondering if there was a way of doing it that doesn't involve having the concatenated values written to a column as a formula. Ideally what I would like it to do is put the concatenated value of Sheet1 together as a variable (I think thats what you call them) and then look up that variable in Sheet2 without Sheet2 creating a concatenated column too.

I'm of the understanding that this might be slower than writing the columns, but there is a reason for my madness.

Let me know what you think, or if i'm barking. Any suggested reading would also be handy, I don't like to bug you guys for the full code straight out.

Cheers,

Tom

[DonkeyOte]

Perhaps you mean something like

=LOOKUP(2,1/((Sheet2!$A$1:$A$100=$A1)*(Sheet2!$B$1:$B$100=$B1)),Sheet2!$C$1:$C$100)

So the above would return the contents of Column C from Sheet2 where the value in Columns A & B on Sheet2 matched the values of both A1 & B1 on the sheet containing the formula.

If that is what you're looking to do I would say

a) yes, this approach is regarded as slow
b) what is the data type of the column you're looking to return from Sheet2 (text,number,mix etc) ?
c) which version of XL are you running ?

[beef4dinner]

The returned data would be text. I'm running Excel 2003.

I've been reading around and some people have suggest using the 'Find' function...

Any pointers you can suggest would be great tho!

Tom

[DonkeyOte]

Tom, a sample file outlining your setup and desired results would be helpful I think.... are you looking for VBA approach exclusively (ie even if you can achieve the same with native formulae) ?

[beef4dinner]

Here we go. I want to effectively run a macro where there is no need to insert any columns in to Sheet2 (DataToLookup) in order to concatenate Column B and C. Ideally the only change to the workbooks layout will be the result of the Vlookup in Sheet1 which is column C.

Does that make sense, i'm confusing myself!

Thanks for your help so far on this DonkeyOte.

Tom

[DonkeyOte]

So in formula terms:

OriginalData!C1:
=LOOKUP(2,1/(DataToLookup!$B$1:B15&DataToLookup!$C$1:$C$15=$A1&$B1),DataToLookup!$D$1:$D$15)
copied down

Yes ?

[beef4dinner]

Spot on.

I'm going to work through this LOOKUP formula, i've never used them before, only ever VLOOKUP's. Interesting.

I suppose this is quite easy to integrate in to a macro then!

Cheers DonkeyOte.

Tom

[DonkeyOte]

Along the lines of ?

Please Login or Register  to view this content.

[beef4dinner]

Genius! I really appreciate your help on this one DonkeyOte.

Just need to workout how it works!

Cheers again,

Tom

[DonkeyOte]

I tried my best to explain the method some time ago on a similar thread, see if this helps at all: http://www.excelforum.com/2053930-post10.html

[beef4dinner]

Strangely enough...I think I get it! Well explained and very clever!