Forum Discussion
Formula help pls- im stuck
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.
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 Replies
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.
- This worked like a charm. Thank you very much.
=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.
- Thank you for this!
