Formula to automatically fill in a Column with preexisting data?

Hi,

This really has me stumped. My workbook has two worksheets, with one table on each page. I am trying to get Column A on worksheet 2 to automatically fill in the initials based on the fact the column B has the same names.

For example.
Worksheet 1 has three fields: Initials, Product, Category. All are already filled in.
Worksheet 2 has a blank initials column. The Product column has the same names as in worksheet 1 already filled in.

sample 1.jpg
sample 2.jpg

Please see the example.

Thank you very much.

Originally Posted by adhawan06

Go to Sheet2, A9, and type the following function to lookup the initials from Sheet 1 (Main Table)

Formula:

=INDEX(MainTable!A:A,MATCH([@Product],MainTable!B:B,0),1)

Cheers!!
Anil Dhawan

Thank you very much, this worked!

One last thing. Can you please break down what the formula "=INDEX(MainTable!A:A,MATCH([@Product],MainTable!B:B,0),1)" does? I do understand that "MainTable!" is a link to that sheet.

Thank you very much Anil Dhawan!

Glad It worked and met your result...

This function is looking up the value thats it. The way Vlookup works in the same way this functions is returning the only difference is that it is picking the left side value whereas Vlookup only picks up value from the right side of the lookup range.

Formula:

=INDEX(MainTable!A:A,MATCH([@Product],MainTable!B:B,0),1)

In the above function, INDEX is going to pick the Intitals from MainTable Sheets, then Match function will look up the row number by looking up between MainTable (Sheet) and "Sheet2" and in last 1 is used to pick the column 1.

Hope this will make clarity.

If your requirement is fulfilled, please mark this thread as SOLVED and *Add Reputation.


Cheers!!!
Anil Dhawan

Thank you very much Anil Dhawan! Will add reputation and mark as solved!