can I use concatenate to identify a range of cells? 1

[afbutlerandassoc]

Here's what I want to do:

lookup array = a58:u58

can I somehow use a concatenate command or something to identify the row number? ie:

=CONCATENATE("A",MATCH(A5,ratename)) (this gives me the desired text: a58)

Is there some way of using this nested in a formula to identify an array?

Thanks

 

[xlbo]

to refer to a range in a formula, use the INDIRECT function
if A1 = "A58"
=INDIRECT(A1) will give you the value in A58 but I'm not entirely sure what you're after Rgds
~Geoff~

 

[iGrant]

Xlbo has the right answer. All you need to do is:

=INDIRECT(CONCATENATE("A",MATCH(A5,ratename)))

 

[afbutlerandassoc]

I like the idea, but it's not the answer I need - I want to identify array a58 thruough u58

I'd like to use a match/concatenate function to identify them dynamically, but I can't seem to get it right - I have to look at a cell in column 1 and see which row it is in on sheet 2 then look for a value that matches a name from sheet 1 and find it in an array on sheet 2 in the row I've just identified on sheet 2.

I'm having trouble with this one ...

 

[DaleWatson123321]

This was somewhat of a challenge. I tried various options, and finally came up with the following working formula.

=IF(I5="","",VLOOKUP(A5,vr,CHOOSE(FIND(I5,VLOOKUP(A5,vr,5,FALSE)&VLOOKUP(A5,vr,7,FALSE)&VLOOKUP(A5,vr,9,FALSE)&VLOOKUP(A5,vr,11,FALSE)&VLOOKUP(A5,vr,13,FALSE)&VLOOKUP(A5,vr,15,FALSE)&VLOOKUP(A5,vr,17,FALSE)&VLOOKUP(A5,vr,19,FALSE)&VLOOKUP(A5,vr,21,FALSE)),4,4,4,6,6,6,8,8,8,10,10,10,12,12,12,14,14,14,16,16,16,18,18,18,20,20,20),FALSE))

The formula looks up the text in column A, and concatenates the codes from the 9 different fields.

The FIND function references the code in the adjacent cell (I5) and is used to locate the position of this code within the concatenated text.

The CHOOSE arguments (positions) reference each of the fields containing the matching VALUE of each code.

Therefore, regardless of which field a code is located, the formula will return the value for that code.

I'm pleased that this solution resolved your rather unique task.

Best Regards, ...Dale Watson dwatson@bsi.gov.mb.ca