Foreach loop lookup between two worksheets

Hi All,

I'm kind of stuck trying to fix a loopup between two worksheets.

Column P of sheet 1, contains multiple references to a Sheet called TOM,

So column P would look something like TOM001.001, TOM002.007

What I need to achieve is for each value in Column P (which corresponds to Column A of worksheet TOM), copy the value in TOM Column B, to Sheet1 column Q comma separated.

A vlookup got it to do it when column P only had one value, but I can't figure out how to do it iteratively.
=VLOOKUP(P2,'TOM'!$A1:$B97,2,FALSE)

If I can get this to work, I might need to add another mapping between TOM column C and Sheet 1 column R

Anyone able to help?

Many thanks
Asparak

Please post an example workbook using fictional / non confidential data that accuartely represents your real one, showing the existing data and the expected results.

If I'm following your question correctly then I don't see any easy way to do what you want without the use of vba.

Made a little progress, by using a series of =MID($P125,1,10) etc statments, then a =IFERROR(VLOOKUP($R125,'TOM'!$A2:$B110,2,FALSE),"") and finally concatenating all the outputs back together again, but it's already a maintenance nightmare doing this. The largest so far is 10 sets of MID and VLOOKUP for a single row. Anyone any brighter ideas?

Looking at your dummy file, the long winded method you've tried is about the only non-vba way of completing the task

I've been trying to put together what should be a simple udf to split the references in column P, perform a lookup on each string, then concatenate the results.

At the moment it's returning errors not results, I'm not going to be defeated, but don't think I'm going to get a working version posted today.

Really appreciate you trying to help. I'm also glad I haven't missed an obvious way of doing this. I did look at doing this as a single nested statement as well, but that I believe will ause me more of a maintenance headache as it would be almost impossible to spot which entry had caused it to fail.

Copy this code to a standard module (in excel press Alt + F11 to open the editor, then insert > module from the editor menu).

Option Explicit
Public Function MLOOKUP(mRef As String, mRng As Range, mCol As Long) As String
Dim mRefArr, a As Long, tmpStr As String
If InStr(mRef, ",") Then
    mRefArr = Split(mRef, ",")
        For a = 0 To UBound(mRefArr)
            tmpStr = tmpStr & Application.WorksheetFunction.VLookup(Trim(mRefArr(a)), mRng, mCol, 0) & ", "
        Next
    MLOOKUP = Left(tmpStr, Len(tmpStr) - 2)
Else
    MLOOKUP = Application.WorksheetFunction.VLookup(mRef, mRng, mCol, 0)
End If
End Function

Then in Q2 use =MLOOKUP(P2,TOM!$A$4:$B$13,2)

Formula entry is the same as VLOOKUP, but without the last optional TRUE /FALSE argument, I've fixed that to false / exact match within the code.

If no match is found the formula will return an error which can be suppressed in the usual way, i.e. =IFERROR(MLOOKUP(P2,TOM!$A$4:$B$13,2),"")

Where a cell contains multiple strings to lookup, i.e. P3 in the dummy file, the function will return the whole set as a single error.

Thank you so much. Really appreciate your time. I'll try that out and let you know.