I have two columns User name and ID in sheet 1 in my google sheet. In sheet 2 I have a list of all the user names and their corresponding ID's.

I have created a drop down list for column User name in sheet 1 with values picking from the user names column range in sheet 2. The challenge is, how do I make it such that whenever I pick a user name from the drop down list in sheet 1 for User name column, It automatically fills the column ID with the matching ID value from sheet 2

CodePudding user response：

Try below formula-

=INDEX(Sheet2!$B$2:$B,MATCH(A2,Sheet2!$A$2:$A,0))

CodePudding user response：

Where targetCol is the column that contains what you want to return, and matchCol is the column with the ID to search for and dropdown is the dropdown cell:

INDEX(sheet2!targetCol, MATCH(dropdown, sheet2!matchCol, 0))

You can use VLOOKUP, but I prefer INDEX/MATCH. To use VLOOKUP, the data on Sheet2 must be set up with the search column on the left of the return column. Then you would do something like this:

VLOOKUP(dropdown, Sheet2!matchCol:targetCol, index)

where index is the number of the column with reference to the data range passed (Sheet2!matchCol:targetCol), and can either be manually determined (eg.: 5 for the 5th column over) or calculated (ie.: targetCol-matchCol 1).