Home > Forum Home > Automating Data Analysis Tasks > Concatenate string and lookup in array? Share

Concatenate string and lookup in array?

Excel Help for Concatenate String And Lookup In Array? in Automating Data Analysis Tasks


Forum TopicPost Reply Login

Concatenate String And Lookup In Array?

Rate this:
(3/5 from 1 vote)
ConfusedHi!
I'm new to this forum, 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 
 Zeteco Energy - Renewable Energy Solutions
 Posted by on
 
Replies - Displaying 1 to 1 of 1Order Replies By: Most recent | Chronological | Highest Rated
Shocked
Rate this:
(3/5 from 1 vote)
If you have drop down boxes, you will want to ensure that the values are set in cells.  If the drop down is from data validation then this is OK - otherwise you will need to use the INDEX function on the source range of a the form object cell link from what has been chosen.

The CONCATENATE function can then be used inside a VLOOKUP function to return the match in the column Q.  Be sure to use the option FALSE at the end to return exact matches only (not the closest with option TRUE).
 Excel Business Forums Administrator
 Posted by on
 Displaying page 1 of 1 

Find relevant Excel templates and add-ins for Concatenate string and lookup in array? in the