Reference Formula Help

  • Thread starter Thread starter Scott
  • Start date Start date
S

Scott

I have a series of data that I'm trying to create a reference for. For
example:

A B C D
1 2 3
2 4 1 5
3 2 1 4
4 3 2
5 1

Data in Column A is the name or search criteria. Columns B through ... is
the range of data I want to search. For this example, for the name or search
criteria of "3", I would like to return the number of names or search
criteria "3" appears in throughout the data range. So, for this example, 3
appears in names 1 and 4.

I tried the initial VLOOKUP search, but realized very quickly that this was
a little more complicated than that. I can have unlimited values, however
none will be duplicates.

Any ideas would be very helpful and much appreciated.
 
Scott if you only require the number of times that your search criteria
appears ("I would like to return the number of names"), try this: (Enter in
cell E1 and copy down)
=COUNTIF($B$1:$D$5,$A1)
Hope this helps
 
Essentially that is correct, however I will have at least 500 rows of data
and I don't want to simply count the number of times Column A comes up, but I
want to return the names of of the data where it appears.

With this example, I want to create another column for each row that simply
states which returns the other linked names.

For example, in Column E, I would like to return the following.
A E
1 2,3
2 1,4
3 1,4
4 2
5 2

Notice Column E searches Columns B, C, and D for what's in Column A and when
there's a match, returns the name in Column A from that matched row.
 
Back
Top