MS Excel: How do I reference one list to find a corresponding value in another list?

Question

Please help!!...I have a spreadsheet with two pages (The information must me separated this way). The first page has two columns. Columns A is a list of ID codes. I want to populate Column B with dollar values. The second page has two columns. I'll call them Column 2A and 2B. Column 2A has a list of IDs that have many values in common with Column A in the first sheet. Column 2B is the corresponding price list for Column 2A. How do I populate Column B in the first page with the corresponding dollar values (from 2B) only for instances in which the IDs in Column A are found in Column 2A?

Answer 1

This will work: =vlookup(2A:2B, x, y) where x=cell A1 on column A of first worksheet and y= highlight entire column 2B on second worksheet.

Answer 2

I'm guessing that the ID codes may be repeated on page 2 several times and you want to produce a total value on page 1? If so, here is a way to go about it:

The two spreadsheets were the generic Sheet1 and Sheet2. Let's start with Sheet2.

Sheet2:
A1 was a title "ID CODE"
A2:A12 - I entered random ID codes, some were repeated.
B1 was a title "COST"
B2:B12 - I entered random costs ranging from $1.97 up to 15.00

Sheet1:
A1 was a title "ID CODE"
A2:A12 contained ID codes to check for
Each one was unique (never repeated) and may or may not have matched the values in Sheet2 column A.

B2 was a title "TOTAL COST"
B2:B12 contained the calculation below (I split it on three lines so that it won't wrap or cut off):
=SUMIF(Sheet2!A2:A12,
Sheet1!A2,
Sheet2!B2:B12)

What this says to Excel is "...Add up all the costs in Column B on Sheet 2 where the ID code on Column A on Sheet 2 matches the ID code in Column A on Sheet 1..."

This should also work if the ID codes in Sheet 2 are unique, since the SUMIF would only pick up one value.

Answer 3

Assuming that your pages are called sheet1 and sheet2 (the defaults), and your values start at row 1, put this formula in cell B1:

=if(a1=sheet2!a1,sheet2!b1,"")

You're saying "if the value in column A of this row matches the value of colum A of this row of the other page, then make this cell equal to the value of column B of this row on sheet 2"

Then just "fill down" this formula as far down the column as you need it.