Compare two columns and return another cell value in the matched row

0
votes

Thanks everyone!

I have 2 sheets in the same workbook. I want cell A2 of Sheet1 to have a formula that looks at the cell on the left (B2, "Company Name") and then searches for a match in Sheet2 column A (the entire column, "Company Name"). If there is a match, then I want Sheet1 cell A2 to populate with the value to the right of the matched cell (Sheet2, column B).

Sheet1 looks like:

 [Account ID]---[Account Name]
 [          ]---[Company 1   ]
 [          ]---[Company 2   ]
 [          ]---[Company 3   ]

Sheet2 looks like:

 [Account Name]---[Account ID]
 [Company 3   ]---[123456    ]
 [Company 1   ]---[736936    ]
 [Company 2   ]---[192563    ]

What is the best way of going about this. Let me know if more explanation is needed! Thanks to everyone!

1
excelexcel-formulaformulavlookup
Just to practice index/match, =INDEX(B:B,MATCH(B1,A:A,0)) should also work. Edit: uh, I am only using 1 sheet. – findwindow
@findwindow - (Don't forget the sheet references) – BruceWayne
Batman, too lazy, just used 1 sheet =P – findwindow

1 Answers

2
votes

This is perfect for a Vlookup() formula. In your Sheet1, Cell A2, use this formula =Vlookup(B2,Sheet2!$A$1:$B$3,2,False).

Adjust the ranges as necessary