Formula help pls- im stuck

[shockliz]

Worksheet #1 has a "Last Name" and a "Dollar amount". Worksheet #2 has a "Last Name" and a "US State". I'm trying to create a formula to move the State from sheet 2 to sheet 1:

If a last name in sheet 1 matches a last name in worksheet 2, then put the state from sheet 2 for that row into sheet 1 in the same row as the match.  The reason I want to move the states over to sheet 1 is so that I can then create a SUMIF to add up my dollar amounts per state. 

[#2]

@shockliz 

=VLOOKUP(A2,'worksheet#2'!$A$2:$B$26,2,FALSE)

=INDEX('worksheet#2'!$B$2:$B$26,MATCH('worksheet#1'!A2,'worksheet#2'!$A$2:$A$26,0))

Both formulas work in my spreadsheet. If you work with Office365 or 2021 you can apply XLOOKUP as well.

[#3 · Accepted solution]

@shockliz 

I believe you are looking for a lookup function (xlookup, index/match, vlookup). Not knowing what version of excel you're using or how the data is laid out, I'll suggest index/match.

If this is worksheet #2:

And this is worksheet #1

Then, as you can see, the formula would be:

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

Adjust cell/range references as needed.

[#4]

Thank you for this!

[#5]

This worked like a charm. Thank you very much.