Microsoft Excel - Concatenate string and find match in array?

Asked By Kasper on 11-Oct-10 06:06 AM
Hi!

I'm new here, so please bare with me, if I make some mistakes.

OS: Win 7 pro
Excel: Excel 2010 Beta

I have 4 seperate drop down lists with input needed to determine the production of a PV system: Location, orientation, tilt and plant size.

I would like to concatenate these 4 inputs, and look for a match value in a column.

To give an example:
Drop-down inputs:
B4 = Copenhagen
B6 = South
B8 = 40 degrees
B10 = 3,8kW

This should be concatenated into "CopenhagenSouth40degrees3,8kW". Then the function should look in column N to find a exact match of this string.
Column N contains the parameters (Selected from the dropdown lists), and column Q contains the production of the PV system (3.640 kWh in this case)
As a final thing, I would like the function to return the value from the Q column.

Is this possible?

I have tried with the following functions:
{=MATCH(CONCATENATE(B4;B6;B8;B10);A18:A30)} 

and

{=VLOOKUP(CONCATENATE(B4;B6;B8;B10);A18:A30;1)} 

Any help is more than welcome.

//Kasper 
Gordon replied to Kasper on 11-Oct-10 09:30 AM
vlookup(a,Inb,dothis,0)
The zero at the end of the vlookup tells it to find an exact match.  If no match it returns error
IF you place 1 at the end it only find closest match.
Excel 2010 also has a neat new commant
iferror(vlookup(a,Inb,dothis,o),dothisiferror)
If the lookup is found, it will 'dothis' if it is not found the vlookup is an error and it wil 'dothisiferror'
Rolf Jaeger replied to Kasper on 12-Oct-10 03:50 AM
Hi Kasper:

there are a couple of problems with the formulae you posted.

1) the parameters of the CONCATENATE function are separated with commas, not with semicolons

2) the second parameter of the VLOOKUP function is the array (for a refresher on the VLOOKUP syntax see e.g. http://officeimg.vo.msecnd.net/en-us/files/818/530/AF101984660.pdf). Since VLOOKUP can only look up values in the first column of the specified array, and since you want column N to be searched you must specify an array that starts with column N.

3) the third parameter is intended to be the index of the column of the specified array from which VLOOKUP should return a value

4) the forth parameter specfies whether an exact is required (FALSE) or an appropriate match is adequate (TRUE).

With this in mind, and if you had only two rows of data starting in cell N1, this formula should do the trick for you:

=VLOOKUP(CONCATENATE(B4,B6,B8,B10),N1:Q2,3,FALSE)

Hope this helped,
Rolf