VLookup with Multiple Answers

  • Thread starter Thread starter idlan
  • Start date Start date
I

idlan

Hello,


The data:

In file A, I have a list of names of directors of firms in my researc
'population', which roughly looks like this -

Firm A // Director A // Status1
Firm A // Director B // Status 1
Firm A // Director C // Status 2
Firm B// Director X // Status 2

and so on (// to indicate next cell)

In file B , I have a list of the firms in my smaller sample, and i
has a unique identifier which relates to file A, meaning I can do
VLOOKUP on it.

The problem:

I would like to do a VLOOKUP on, say, Firm A based on the list in m
second file, but it only returns the findings for Director A, o
sometimes gives me a #N/A.

The question:
How do I do a VLOOKUP and get the values for all the firms in File B
when for each unique identifier in File A, there are multipl
responses?

Thanks in advance
Idla
 
Hi

Depending on the information you are trying to retrieve, and how complex it
is, you could possibly do the job with Data/Filter/Autofilter.

Andy.
 
Hi
one way: Enter the following array formula on your second sheet
(assumption: formula is entered in row 1)
=INDEX('sheet1'!$B$1:$B$100,SMALL(IF('sheet1'!$A$1:$A$100="Firm
A",ROW($A$1:$A$100),1000),ROW()))
and copy down
 
Back
Top