EXCEL: Need to find a value in a range of cells from another worksheet and return value from adjacent cell

Question

I am trying to find the formula that will search in column A (of worksheet "ABC") to find a value matching G4 in worksheet "XYZ" Once that value is found, return the value of the cell adjacent (column B of "ABC") to the cell in which this formula exists in worksheet "XYZ". All sheets are in the same workbook.

Thank you so much!

Solution 1

here's an example of what you're describing:

In spreadsheet ABC, you have a reference value in column A and data in column B

In spreadsheet XYZ, you have a matching number in column A. You'd like to pair the data from spreadsheet ABC to the value in XYZ:

If you notice the formula in the formula bar on the second picture, you'll see the vlookup formula to pull the data for this example. I also added an apostrophe in front of the formula in cell B1 (image 2) to have it display the formula. Note the formulas are slightly different since they point to different reference cells.

Also, here's a great reference for how the vlookup function works:

http://chandoo.org/wp/2012/03/30/comprehensive-guide-excel-vlookup/

Solution 2

Here is another solution that's closer to your adjusted question.

This solution still uses the vlookup formula. Instead of using it to associate a value on multiple rows, you can look-up a single value. (same function, different application)

Again, I'll point you to a great reference for how the vlookup function works:

http://chandoo.org/wp/2012/03/30/comprehensive-guide-excel-vlookup/

Comments

[John Y]

This is a very nice answer that helps the OP solve his immediate problem and provides further reading.

[Admin]

Thank you so much for the guidance so far! Is there any way to have it "search" for the value in column A? Sorry, I am very new to this and trying my best to make a clear post. Here is a better explanation.. On Sheet ABC, Column A has dates in it (ie 1/1/2013) and dollar amounts in Column C, If I enter (or change) the date in A2 on XYZ and that date is found in column A on ABC, I want the dollar amount in column C (adjacent to the date in A on ABC) to be entered in a designated cell on sheet XYZ.

[Adam]

Nice example indeed, but when providing samples using images, always make sure you also provide the actual formula/code as text.