Comparing two tables. Bibliography and members

[mrpwagner]

Colleagues,

I need to find members of an organization in a bibliography.

In a cell for the bibliography the authors will be LN1, FN1, LN2, FN2, LN3, FN3......etc..... [Let's call this column BA]

In the member excel sheet there will a column with the last names. [Let's call this column MLN]

So I will need to find where MLN appears in the column BA in the bibliography.


I am sure there is a way..... just a bit beyond my level.

Thanks in advance.

 

[Repush]

try

=MATCH(MLN2,BA2:BA20,0)

 

[mrpwagner]

try

=MATCH(MLN2,BA2:BA20,0)

I am not sure this will work.....

I have a member list of almost 300 persons and a bibliography list of 500 entries. Many of the entries have more that one author. I will need to cross reference EACH individual member against the several authors in each bibliography entry. Perhaps I need to express myself as looking for a cell in a column that contains the text in the source cell.

Any assistance would be helpful.

thank you.

MW MD

 

[Repush]

you might need a VBA-Function for for this, or maybe there's an arrayformula-wizard that can do this with a function.
please give a clear example of your data and the expected result

 

[Repush]

this might do the trick:

Function GetBooks(Books As Range, author As Range) As String
    Dim c As Range
    Dim searchStr As String
    Dim searchIn As String
    Dim pos As Long
    
    GetBooks = ""
    searchStr = CStr(author.Value)
    For Each c In Books
        searchIn = CStr(c.Offset(0, 1))
        pos = InStr(searchIn, searchStr)
        If pos > 0 Then
            GetBooks = GetBooks + CStr(c.Value) + ", "
        End If
    Next c
    GetBooks = Left(GetBooks, Len(GetBooks) - 2)
End Function

enter "=GetBooks([booklist],[authorname])"
The code expects a string with all the authors' names in the first cell to the right