Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I've got two reports, pulled in as separate sheets in one excel file. This is the approach I came up with, but I don't know how to implement it:

  1. For each cell in column "merchant_name" in sheet 1, find the cell with the matching value in the "merchant_name" column in sheet 2. (This will be a one-to-one relationship.)
  2. Identify the row for the found cell
  3. Check this row for the value of the "rank" column
  4. Print the found "rank" column value to the "rank" column row in sheet two that corresponds with the matched merchant_name value.

...does that make sense? If someone could help me understand what functions these would be to google and try to piece together a formula, that would be uber helpful also.

share|improve this question

migrated from Mar 9 '13 at 9:23

This question came from our site for professional and enthusiast programmers.

have you tried a vlookup? – nathan hayfield Mar 8 '13 at 22:54

If you're only doing this once, and there is truly a one-to-one relationship of merchant names, you could just sort both sheets by merchant name and then copy the rank column from one to the other, then unsort the sheets (usually accomplished by adding a placeholder column before sorting and filling it with 1 2 3...)

share|improve this answer

If the "rank" column is to the right of the "merchant_name" column, you can use a VLOOKUP formula like so...

=VLOOKUP(A2, Sheet2!A:B, 2)

... where A2 is the cell on Sheet1 containing the "merchant_name", Sheet2!A:B is the range of data on sheet 2 containing at least the "merchant_name" and "rank" columns, and 2 is the index number of the "rank" column in relation to the range of data. So in this case 2 would refer to column B (the 2nd column in the range of data).

Then you can copy that formula down the column.

share|improve this answer

You've sketched out a good plan for doing this task.

I would suggest you use the MATCH function to get the row in Sheet2 with a merchant name that corresponds to the one you are looking for in Sheet 1. The functions syntax is MATCH(value_or_cell reference_to_be_matched, range_to_be_searched, type_of_match) (You'll want to use 0 for the type of match, since you want exact match.

To fetch the rank for the matched merchant name, there are several options. I think INDEX would be best: INDEX(range_to_be_searched, row_number, column_number). The range would be the rank column, the row number the result of the merchant name match, and the column number 1 (the numbering is relative to the first row in the column; and since it's a single column, it's the first column in the index range.).

These functions can be used separately or combined into a single formula.

share|improve this answer

You must log in to answer this question.