Home > Net >  Formula to auto populate cells in excel based on another cells information
Formula to auto populate cells in excel based on another cells information

Time:01-21

I have a sheet where one column is zip codes in which a sale is made, the next column are the initials of the salesmen who made the sale. The zip codes and initials will always correspond to each other.

For example, 60604 would be initials TY and would always be the same initials.

I tried to sort by zip code and do it by hand and by using autofill but it became too time consuming with over 5000 rows

CodePudding user response:

I would use vlookup() or index() with match() and a lookup table:

=vlookup(A1,A10:B12,2,0)

Where A1 contains 60604 and the table is of the form:

60604 TY 60605 JD 60606 PD

=index(B10:B12,match(A1,A10:A12,0))

Assuming the table is in cells A10:B12

CodePudding user response:

On a second sheet have a list of 2 columns, Zip code and Salesman, with each record being unique, one row per salesman. This will be your lookup table.

  • 27245 - TD
  • 21484 - JM
  • 15479 - FD

Then put this formula in the main sheet where you want the initials to appear

=VLOOKUP(A2,Sheet2!A:B,2,FALSE)

I'm using A2, assuming your table has a header row (so you're starting from row 2) and A is the column where the zip code is. Sheet2 is the lookup table, column A is zip code, Column B is the salesman initials.

  • Related