Insert value from one table into another table, if the previous column had the same value.

Question

My table on sheet 1 has an ID number and a unique username associated for each number.

ID Number	Username
1	kitty
2	puppy

My table on sheet 2 is a list of spending, with columns ID Number, Full Name, and Expenditure. I want to add in their username as a new column.

ID Number	Name	Username (new column)	Expenditure
1	Dan		20
1	Dan		30
2	Molly		16
1	Dan		28
2	Molly		21

I need Excel to insert the username from table 1 into the Username column on table 2, if the ID number is the same. At the moment I'm using the FILTER function as such, however, it seems overly complicated:

=FILTER('Sheet1'!B:B, 'Sheet2'!A2='Sheet1'!A:A)

I tried to use the IF function, however, the expenditure table has more rows than the username table, as there are repeated spendings of individuals.

=IF('Sheet2'A2='Sheet1'!A2, 'Sheet1'!B2)

Is there a simpler way to insert values from another table, dependent on the previous columns number?

Answer 1

Hi ThomasSullivan7,

I’m Daniel and I will be happy to help you with your question, I am a Microsoft consumer like you.

Try

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

I hope this information helps. If you have any questions, please let me know and I'll be glad to assist you further

Best Regards,

Daniel

Answer 2

First of all, I would transform the two data ranges into structured tables. This will eliminate the use of sheet names as it doesn't matter where in the workbook you placed these tables, and you can avoid direct cell references that span entire columns I case you don't know how large the lookup_range needs to be.

Then, since you mention to have tried FILTER, your Excel version supports XLOOKUP. Use that function in stead of VLOOKUP.

=XLOOKUP([@[ID Number]],Table1[ID Number],Table1[Username],"-")