Concatenate formula

[Eroll]

I would like to do a lookup or sum if, the field I am looking up or summing is in two columns, how can I incorporate the concatenate into my lookup formula?

Help would be very much appreciated.

 

[obiron]

put the concatenated value into another column and then use that. If you are using Vlooklup it will need to be the leftmost column of your data. You could alternatively use INDEX() and then OFFSET() from the indexed cell to find the correct value to calculate

 

[Expiry]

you can do =vlookup(a1&b1,C:D,2,0) quite easily if that's what you mean.

ie. if a1 has John, b1 has Smith and in C:C you have JohnSmith.