How to cross-reference two tables

Excel Excel 2007
January 9, 2011 at 14:47:32
Specs: Windows 7
I have two tables. Table 1 has an automatically populated 1st column and the 2nd column is based on a lookup of the second table. Table 2 has a 1st column which contains a single word which would be a substring of the table 1 column 1 data and the 2nd column is an associated category.

Table 1

    A                                     B

1   010310 McDonald's Sydney              Restaurant

2   ref 324323 Ingles Grocery S Bronx     Grocery

3   BP Gas Quickstop #2321                Gasoline

4  010510 Ingles Grocery Store 121        Grocery

(In the table above, the B column would be where the results are placed from the lookup)

Table 2

     A                    B

1    McDonald's           Restaurant

2    Ingles               Grocery

3    Quickstop            Gasoline

So basically for each cell in Table 1 Column B, the formula will need to check if any of the words in Table 2 Column A match any part of the text in the corresponding Table 1 Column A in the same row and then return the information from Table 2 Column B in the corresponding row to the matching word.

Either VBA code or a formula are fine with me.

Thanks in advance for your assistance.

See More: How to cross-reference two tables

Report •

January 9, 2011 at 17:44:07
re: "Either VBA code or a formula are fine with me."

How about a combination of both, which is known as a User Defined Function (UDF)?

I am going to assume that Table 2 resides in Sheet2!A1:A3.

Press Alt-F11 to open the VBA editor
Under Insert, choose Module
In the pane that opens, paste in this code:

Function GetStore(ByVal target As Range)
 For Each cell In Sheets(2).Range("A1:A3")
  If target Like "*" & cell & "*" Then
    GetStore = cell.Offset(0, 1)
    Exit Function
  End If
  If GetStore = "" Then GetStore = "Business Not Found"
End Function

Back in the sheet that holds Table 1, enter this in B1 and drag it down:


Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

January 10, 2011 at 10:42:30
Thank you so very much! This worked splendidly. I modified it slightly to use a dynamic named range. This will save me a tremendous amount of time. Thanks again!

Report •

March 19, 2011 at 23:26:44
i extract the data for sheet1 to another sheet2 by using vlookup.after extracting data is correct or not. example sheet1
name designation code
abc president 001
xyz vp 002

code designation
001 president(using vlookup)
002 vp(using vlookup)
how to check the extract data sheet2 is correct or not?

Report •

Related Solutions

Ask Question