VLookup with Wildcard & Table Query

[shre0047]

I have two different queries being pulled from SharePoint. Table 1 has a column which references the unique ID from Table 2. The caveat is the unique ID cell can have multiple IDs separated by spaces (3).

Table 1 example:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Number[/TD]
[TD]Name[/TD]
[TD]Phone Number[/TD]
[TD]Zip Code[/TD]
[TD]Story ID(s)[/TD]
[/TR]
[TR]
[TD]14A[/TD]
[TD]Paul[/TD]
[TD]555-5555[/TD]
[TD]90210[/TD]
[TD]Lighter1[/TD]
[/TR]
[TR]
[TD]93[/TD]
[TD]Kim[/TD]
[TD]777-7777[/TD]
[TD]90210[/TD]
[TD]Lighter2 Goal3a[/TD]
[/TR]
</tbody>[/TABLE]

Table 2 example:
[TABLE="class: grid, width: 510"]
<tbody>[TR]
[TD]Story ID[/TD]
[TD]Text[/TD]
[TD]Cross Check[/TD]
[/TR]
[TR]
[TD]Lighter1[/TD]
[TD]Ability to light a fire[/TD]
[TD]Flag as this story ID is included in 14A[/TD]
[/TR]
[TR]
[TD]Goal3a[/TD]
[TD]Ability to score a goal[/TD]
[TD]Flag as this story ID is included in 93[/TD]
[/TR]
[TR]
[TD]John316[/TD]
[TD]Ability to convert a table[/TD]
[TD]Don't flag as this story ID isn't in table 1[/TD]
[/TR]
</tbody>[/TABLE]


'Cross Check' column is the information I'm hoping to get. I don't have real data yet as this is in the early stages and being discussed. I want to check if it's possible before going down this path.

 

[steve the fish]

Copying and pasting your two tables into A1 of a new sheet you could use this formula in C7:

=INDEX($A$2:$A$3,MATCH(TRUE,ISNUMBER(SEARCH(A7,$E$2:E$3)),0))

It requires entering CTRL-SHIFT-ENTER not just ENTER.

It will return the number in table 1.

 

[HackSlash]

There are lots of things you CAN do. You could split the cell by spaces in a large number of different ways. You can also add "*" to VLOOKUP to do wildcard lookups. Most things are possible.

 

[shre0047]

You could split the cell by spaces in a large number of different ways.

I thought about this idea, but there may be some rows where they have 20+ Story IDs. Also, if I choose to separate into different cells, it will be gone when I refresh the table as the data source from SharePoint isn't split.

You can also add "*" to VLOOKUP to do wildcard lookups.

I originally thought this would be the best method. However, if they are referencing tables, would the wildcard option work? Something like this:
=VLOOKUP(Table_query2[StoryID],'Table1'!A:E,5,FALSE)

 

[shre0047]

Copying and pasting your two tables into A1 of a new sheet you could use this formula in C7:

=INDEX($A$2:$A$3,MATCH(TRUE,ISNUMBER(SEARCH(A7,$E$2:E$3)),0))

It requires entering CTRL-SHIFT-ENTER not just ENTER.

It will return the number in table 1.

This is interesting. So Table 2 would be pasted from cell F1 correct?

 

[steve the fish]

This is what it produces:

[TABLE="width: 503"]
<tbody>[TR]
[TD]Number[/TD]
[TD]Name[/TD]
[TD]Phone Number[/TD]
[TD]Zip Code[/TD]
[TD]Story ID(s)[/TD]
[/TR]
[TR]
[TD]14A[/TD]
[TD]Paul[/TD]
[TD]555-5555[/TD]
[TD]90210[/TD]
[TD]Lighter1[/TD]
[/TR]
[TR]
[TD]93[/TD]
[TD]Kim[/TD]
[TD]777-7777[/TD]
[TD]90210[/TD]
[TD]Lighter2 Goal3a[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Table 2 example:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Story ID[/TD]
[TD]Text[/TD]
[TD]Cross Check[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lighter1[/TD]
[TD]Ability to light a fire[/TD]
[TD]14A[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Goal3a[/TD]
[TD]Ability to score a goal[/TD]
[TD]93[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John316[/TD]
[TD]Ability to convert a table[/TD]
[TD]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col></colgroup>[/TABLE]