Comparing two excel sheets and finding max value AND copying neighbor cell

0
votes

I have two sheets, with similar formatting but different values.

Here's an example (real spreadsheet of course has more values)

Sheet 1

Temp | Year 89 | 1979

Sheet 2

Temp | Year 77 | 1998

I want to compare the Temp Values and put the highest value in a 3rd sheet, and copy the neighboring year value into the neighboring cell in the 3rd sheet. So what I have as a result in the 3rd sheet by using this formula: =MAX(Sheet1!B4,Sheet2!B4) is:

89

But what I'd like is (by referencing the value in the next column and placing it in next column on 3rd sheet):

89 | 1979

Does anyone have an idea how to do this?

Thanks for your time,

S

1
excelexcel-formula

1 Answers

1
votes

I would suggest something like:

=iif(Sheet1!B4 = MAX(Sheet1!B4, Sheet2!B4), Sheet1!C4, Sheet2!C4)

But you will have an issue if the max value is the same for both. Of course you can account for that if it is a possibility and a problem.

Actually, that is not a very efficient way of doing this. This would be better:

=iif(Sheet1!B4 > Sheet2!B4, Sheet1!C4, Sheet2!C4)

And what the heck, if you need to have both C column values when the B columns are equal, you would do something like this:

=iif(Sheet1!B4 > Sheet2!B4, Sheet1!C4, iif(Sheet1!B4 = Sheet2!B4, Sheet1!C4 & ", "& Sheet2!C4, Sheet2!C4))