Muliple lookup

[stapuff]

In sheet Tracker range L3 I have data that looks like the following:

152123; 152124; 152161; 152189

I want to basically vlookup all of the above values in sheet "Data" range O3:O600 and return the value from column X to sheet Tracker M3.

Sheet Data is setup like

ColumnO ColumnX
152123 Jun
152124 Jun
152161 Jul
152189 Sep


So L3 looks like:
152123; 152124; 152161; 152189

Result returns
Jun; Jun; Jul; Sep

Any thought on a formula or vba would be greatly appreciated.

stapuff

 

[stapuff]

Hey Domenic -

I appreciate all your help. This is some awesome stuff.

I checked out your website a little.....is it active and something you are posting on quite a bit?

stapuff

 

[Domenic]

Hey Domenic -

I appreciate all your help. This is some awesome stuff.

You're welcome!

I checked out your website a little.....is it active and something you are posting on quite a bit?

Actually, I haven't posted anything to it recently...

 

[stapuff]

I modified the project a little and getting some odd behavior. I now have 2 columns in sheet "Champ" that look at sheet "Data" and return back to sheet "Champ" the results in column "T"

Looking for your thoughts.....

On sheet "Champ":

P32 = 154727; 154728; 154739; 154740; 154759; 154760
S32 = 154367

T32 = {=IF(AND(S32="",P32=""),"",SUBSTITUTE(AConcat(IF(Data!$O$2:$O$496<>"",IF(ISNUMBER(FIND(";"&Data!$O$2:$O$496&";",";"&SUBSTITUTE(P32," ","")&";")),"; "&Data!$X$2:$X$496,""),"")&IF(Data!$AB$2:$AB$500<>"",IF(ISNUMBER(FIND(";"&Data!$AB$2:$AB$500&";",";"&SUBSTITUTE(S32," ","")&";")),"; "&Data!$AK$2:$AK$500,""),"")),"; ","",1))}

T32 = #VALUE

Note in the formula above $O$497 and $X$497 if I change them both to $500 and ctrl/shift/enter the formula all works perfect.

The formula range is getting automatically changed when I update the access query on sheet "Data" that provides the information.

Thanks,

Stapuff

 

[Domenic]

If the following formula does not return the desired result, provide a small sample of the source data, along with the criteria, and the actual expected result based on the small sample of data.

=IF(AND(S32="",P32=""),"",SUBSTITUTE(AConcat(IF(Data!$O$2:$O$496<>"",IF(ISNUMBER(FIND(";"&Data!$O$2:$O$496&";",";"&SUBSTITUTE(P32," ","")&";")),"; "&Data!$X$2:$X$496,""),"")),"; ","",1)&"; "&SUBSTITUTE(AConcat(IF(Data!$AB$2:$AB$500<>"",IF(ISNUMBER(FIND(";"&Data!$AB$2:$AB$500&";",";"&SUBSTITUTE(S32," ","")&";")),"; "&Data!$AK$2:$AK$500,""),"")),"; ","",1))

...confirmed with CONTROL+SHIFT+ENTER.

 

[stapuff]

Domenic -

Is there a way to change the following 2 formula's to return results regardless if they are a number or text?

In both columns L & O I get data that looks like

123456; 123457; 123458
or
123456-10; 123456-11; 123456-12

The first group of numbers work (without the dash) the second fail. I tried replacing ISNUMBER with ISTEXT but it did not work. Any suggestion?

Thanks,

Stapuff



=MySum(IF(ISNUMBER(FIND(";"&O_Range&";",";"&SUBSTITUTE(L13," ","")&";")),P_Range))

=IF(AND(O13="",L13=""),"",SUBSTITUTE(AConcat(IF(Load_O_Range<>"",IF(ISNUMBER(FIND(";"&Load_O_Range&";",";"&SUBSTITUTE(L13," ","")&";")),"; "&Load_X_Range,""),"")&IF(Load_AB_Range<>"",IF(ISNUMBER(FIND(";"&Load_AB_Range&";",";"&SUBSTITUTE(O13," ","")&";")),"; "&Load_AK_Range,""),"")),"; ","",1))

 

[Domenic]

As far as I can see, the formula should return the desired result, whether the data contains text or numerical values. Try...

=IF(AND(O13="",L13=""),"",SUBSTITUTE(AConcat(IF(Load_O_Range<>"",IF(ISNUMBER(FIND(";"&Load_O_Range&";",";"&SUBSTITUTE(L13," ","")&";")),"; "&Load_X_Range,""),"")),"; ","",1)&"; "&SUBSTITUTE(AConcat(IF(Load_AB_Range<>"",IF(ISNUMBER(FIND(";"&Load_AB_Range&";",";"&SUBSTITUTE(O13," ","")&";")),"; "&Load_AK_Range,""),"")),"; ","",1))

...confirmed with CONTROL+SHIFT+ENTER.

 

[stapuff]

Domenic -

I appreciate your confirmation that the formula should work. After looking further - there is a space to the left of the "-", which I did not see. I was able to remove the space and it works now.

Thanks for your help again,

Stapuff