CODEWITHSUNDEEP
excelexcel-formula
Chemdawg
Chemdawg

Reputation: 69

Formula to gather multiple values on Vlookup function

i know its not possible, but i need a way I can look at a data set such as:

HAZDAMAGE   2   164601
HAZDAMAGE   2   228934
MHAZ-001    168 38838
MHAZ-001    168 82843

and return those values in the third column into rows underneath the main column:

HAZDAMAGE   MHAZ-001
164601      38838 
228934      82843

I've been having a hard time figuring out a good formula to use

Upvotes: 0

Views: 94

Answers (1)

Mike K
Mike K

Reputation: 66

I think what you said you tried is the right way to do this, using a helper column. I'd create another column that keeps a count of how many times each value has appeared so far concatenated with the value itself. In your case something like this in the fourth column D (and fill the formula down):

=COUNTIF($A$1:A1,A1)&"-"&A1

Then wherever you want to list all the matches (suppose your first list heading is in F1), you could do this with index match (and then fill down to get subsequent matches):

=INDEX($C$1:$C$4,MATCH(ROW(A1)&"-"&F$1,$D$1:$D$4,0))

Upvotes: 1

Related Questions