VLOOKUP with multiple matching IDs

[JackDanIce]

Hi,

I have some data which I need to aggregate, Excel's consolidation feature works fine for numbers but not strings for understandable reasons.

Column B contains look up values in alphabetical order, data range is $A$4:$CI [last row]

My code currently applies the consolidation feature and outputs the condensed data 2 rows after the [last row] e.g. $A$12:$CI$15 if [last row] previously was 10

I then use VLOOKUP in empty columns (specifically where string values are expected) to fill in these values, but because there are multiple matching IDs in A4:CI10, it returns error.

Aside from a brute, evaluate each column and copy value where condition matches, can anyone suggest anything more elegant?

Effectively ask is:

I want to match on an ID in a list of sorted IDs (A-Z) which includes duplicates but only return a value if it's non-numerical and not empty.

Code or formula suggestions are both welcome.

TIA!
Jack

 

[steve the fish]

If im understanding correctly try something like:

=LOOKUP(2,1/((A2:A10="ID")*NOT(ISNUMBER(0+B2:B10))),B2:B10)

 

[JackDanIce]

Solution found:

=VLOOKUP(A19,INDIRECT("$A" &MATCH("*",N4:N14,0)&":$CI$14"),COLUMN(),0)

Code already uses:

vlookup = "=VLOOKUP($A" & .Row & "," & rngRef.Address & ",COLUMN(),0)"

So just need to adjust the 2nd argument to use the required lookup range

 

[JackDanIce]

Thanks @steve the fish I keep forgetting about LOOKUP(2/1) but that would work also.

I'm going to see if I can adapt my suggestion as code already uses a similar string for formula and if I can change the input parameter to vary for this - otherwise will try your suggestion