-3

Disclaimer: I am super new to using any of the complex formulas, I have only ever had to do basic formulas. I am using Microsoft Excel and have two tables. Table 1 has a list of locations, including a zip code and a team name. Table 2 has a list of companies with information including a zip code for their location. I am trying to match the zip code of the company in table 2 with the zip code in the table 1, and return the team name in an additional column in table 2 that I can filter on.

I have tried searching stackoverflow and tried implementing a few of the solutions I found including IF statements, VLookUp, and Index. The previous posts I found were not using tables, I am not sure if that makes a difference. 

Table 1
Team Red    12345
Team Blue   12346
Team Green  12347
Team Red    12348
Team Blue   12349
Team Green  12344

Table 2
Company A   12345
Company B   12346
Company C   12347
Company D   12348
Company E   12349
Company F   12344
Company H   12345
Company I   12346
Company J   12347
Company K   12346
Company L   12348

The third column is what I want to return and filter on:
Company A   12345  Team Red
Company B   12346  Team Blue
Company C   12347  Team Green
Company D   12348  Team Red
Company E   12349  Team Blue
Company F   12344  Team Green
Company H   12345  Team Red
Company I   12346  Team Blue
Company J   12347  Team Green
Company K   12346  Team Blue
Company L   12348  Team Red
cerasaurus
  • 7
  • 3
  • 2
    Look into INDEX/MATCH: https://stackoverflow.com/questions/38641040/excel-vlookup-where-the-key-is-not-in-the-first-column/38641090#38641090 – Scott Craner Aug 27 '19 at 18:35
  • = INDEX(Table2[#All], MATCH(Table1[@Zip], Table1[#All],0)) I am trying to use this, what I get when I select the entire range for Table 1 and Table 2. Table 1 zipcode is where I would want to get the value for the the index I would want to get to return the value in Table 1, column Team Name. – cerasaurus Aug 27 '19 at 19:19
  • MATCH needs to be only one column not the whole table and the INDEX should be the column in which the values to be returned are located. – Scott Craner Aug 27 '19 at 19:20
  • I get #N/A with this = INDEX(AD:AD, MATCH(Table1[@Zip],I:I,0)) AD:AD = Company Name Table1[@Zip] = The zip code assigned to a team name I:I = Team name – cerasaurus Aug 27 '19 at 19:29

1 Answers1

0

Assuming the data from the first table is from A1:B6 with no headers, this is the formula you need in the other tables third column. I'm also assuming the second table starts at A10:B22

=index($A$1:$B$6,match(B10,$B$1:$B$6,0),1)

Readjust the references accordingly.

We'll break this down into sections

=index([sec 1],match([sec 2],[sec 3],[sec 4]),[sec 5])

  1. This is table you are trying to pull the data FROM
  2. This is what you're using to find that data (this would be the cell with the 123450.
  3. This is the column in the table your getting data from that has those 12345 codes.
  4. Use "0", this means exact match, just a binary way of asking what type of match you want, in this case you want exact.
  5. What column is the data your trying to pull in. Starting from the left, the leftmost column being 1.
Mark S.
  • 1,474
  • 1
  • 6
  • 20
  • I get #N/A when trying to use this as well (my table did have headers) =INDEX($X$2:$AD$3562,MATCH(Table1[@Zip],$A$2:$A$11961,0),1) $X$2:$AD$3562 = table 1 with zip and team name Table1[@Zip] = zip code from table 1 $I$2:$I$11961 = column in table 2 with zip codes – cerasaurus Aug 27 '19 at 19:52
  • `=INDEX($X$2:$AD$3562,MATCH(Table1[@[Zip]],$A$2:$A$11961,0),[put the column number of cells you want pulled here])` You also had your lookup value referenced incorrectly. I adjust it to be be `Table1[@[Zip]]` – Mark S. Aug 27 '19 at 19:59
  • So if the value I wanted to return is in Column AD, your saying : =INDEX($X$2:$AD$3562,MATCH(Table1[@Zip],$A$2:$A$11961,0),$AD:$AD)? – cerasaurus Aug 27 '19 at 20:06
  • No, I mean a number like if your range is from columns C-F, column F would be 4. So you go from X:AD, but the column you want to pull is AD so it would be 7. – Mark S. Aug 27 '19 at 20:09
  • Sorry, I just realized I didn't correct the typo in the original formula: `=INDEX($X$2:$AD$3562,MATCH(Table1[@[Zip]],$I$2:$I$11961,0),7) $X$2:$AD$3562 = table 1 with zip and team name Table1[@[Zip]] = zip code from table 1 $I$2:$I$11961 = column in table 2 with zip codes 7 = column number I want to return when the zip code matches ` – cerasaurus Aug 27 '19 at 20:23
  • Thank you so much for your help! I didn't realize you had edited your original answer. I may have had it right a few times but the issue actually ended up being that the zip code from the lookup table was formatted differently than the other zip code column. – cerasaurus Aug 28 '19 at 15:53
  • @cerasaurus that should be your starting point, look for those green triangles on the edges or the cells. Exports will sometimes have numbers stored as text. – Mark S. Aug 28 '19 at 16:05