Excel formula to compare values in 2 different sheets and paste data from another column

Question

I need to compare data between 2 sheets and paste data from another column if there a match. For example sheet1:

Column1	Column2
ID	data
2	21
3	34
4	46

Sheet2:

Column1	Column2
data
21
34
46

So I need to compare data from Sheet1 Column2 with data from Sheet2 Column1 and if they match it would paste the data from Sheet1 Column1 into Sheet2 Column2. So in Sheet2 Column2 the row with nr 21 should show nr 2 etc.

Answer 1

Have you tried any one of the following, you need to change the range accordingly with your data

1.) Formula used in cell E3 applicable to Excel 2021 & O365 Users

=XLOOKUP($D3,$B$3:$B$5,$A$3:$A$5,"Not Found")

2.) Formula used in cell F3 =IFERROR(INDEX($A$3:$A$5,MATCH($D3,$B$3:$B$5,0)),"Not Found")

3.) Formula used in cell G3

=IFERROR(VLOOKUP($D3,CHOOSE({1,2},$B$3:$B$5,$A$3:$A$5),2,0),"Not Found")

This requires CTRL SHIFT ENTER

4.) Formula used in cell H3 =IFERROR(LOOKUP($D3,$B$3:$B$5,$A$3:$A$5),"")