vlookup with match to return a result with multiple rows with lookup value

[excelnoob_67]

So I have a doozy of a question.

I have 2 tables.
Table 1: contains a list "Sites=siteID" on each row. Columns contain "Device Name" 1-30
Table 2: contains "SiteID", "Device Name" SiteID is over multiple rows

See examples
Table 1:

Site ID	Device Name 001	Device Name 002	Device Name 003
7412	LAP7412001	LAP7412002	LAP7412003
9632	LAP9632001	LAP9632002	LAP9632003

Table 2:

7412	LAP7412001
7412	LAP7412002
7412	LAP7412003
9632	LAP9632001
9632	LAP9632002
9632	LAP9632003

Now what I would like to do is match / vlookup the data in table 2 and update table 1 column Device Name.
In my head it would be something like:
lookup 7412 if device name = 7412001 add it column 001
lookup 7412 if device name = 7412002 add it column 002
And so on and so on.

first issue is vlookup wont look past the first siteID on table 2. Table is not a large export and can be formatting another way to make is easier to look up.
I guess the real question is how can I tell excel if the siteid matches and the device id ends in 001/2/3 add to the relevant columns

This isn't a simple transpose will fix situation

Any help would be appreciate

Thanks

 

[FormR]

can we adjust the formula to lookup the first say 1200 rows opposed to the entire column?

The formula you posted as your "final formula" in post#9 already used a limited number of rows, but either way, just try it and see if it helps.

 

[excelnoob_67]

=IF(J$2="","",INDEX(FILTER('Store Scope STS'!$B:$B,('Store Scope STS'!$A:$A=$A46)*(RIGHT('Store Scope STS'!$B:$B,LEN(J$2))=J$2),"Not in Scope"),1))

Is the above looking in the entire column A?
i was thinking $A1:$A1200
would that only look at the the 1200 rows

 

[FormR]

Just add the rows to the ranges for example.. $B$1:$B$1200

 

[excelnoob_67]

do i do that across all the the formulas?

=IF(J$2="","",INDEX(FILTER('Store Scope STS'!$B$1:$B$1200,('Store Scope STS'!$A$1:$A$1200=$A46)*(RIGHT('Store Scope STS'!$B$1:$B$1200,LEN(J$2))=J$2),"Not in Scope"),1))

Like the above?

 

[FormR]

Yes, that looks ok to me. What happened when you tried it?

 

[excelnoob_67]

Looks good so far thanks