1

I have two ranges on the same sheet, we'll call them Range A and Range B. These ranges are the same size and their location on a sheet is known.

I ultimately need to find a value in Range B. To start with, I have supplied a value in an arbitrary cell. I need to find this value in Range A. Based solely on the relative location of the value in Range A, I need to find a different value in Range B.

I have been able to locate the cell using

ADDRESS(SUMPRODUCT()ROW(),SUMPRODUCT()COLUMN())

function where C32:H43 is Range A and the cell value is K33:

=ADDRESS(SUMPRODUCT((C32:H43=K33)*(ROW(C32:C43))),SUMPRODUCT((C32:H43=K33)*(COLUMN(C32:H32))))

In the following picture, the given value is underlined in red. Range A has the value, and it is highlighted in green. I need the value highlighted in Blue in Range B.

enter image description here

Improve this question
0

1 Answer 1

Reset to default
0

Since there is only a single match, and the values are numeric, you can use:

=SUM((RangeA=[Value_to_Match])*RangeB)

or, with a cell reference:

=SUM((RangeA=K33)*RangeB)

enter image description here

Improve this answer
3
  • This worked; thank you! Although, I admit that I am not sure how it works; can you explain it?
    – Lost_Llama
    Commented Nov 27, 2023 at 12:28
  • @ThirstyVoltage I think if you enter the parts of the formula sequentially on a worksheet you will see how it works. First enter (RangeA=[Value_to_Match]). Then enter (RangeA=[Value_to_Match])*RangeB. Both should result in arrays the same size as the original. In Excel, True = 1 and False = 0
    – Ron Rosenfeld
    Commented Nov 27, 2023 at 13:51
  • Good idea, I did that and yes, now it makes sense why it worked. Thank you very much.
    – Lost_Llama
    Commented Nov 27, 2023 at 14:54

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .