0

I would like to have a cell reference another with an IF statement like this:

IF the value of any Cell in Column A of Sheet 1 is 100 then this cell (Sheet 2, cell A2 for example) should be the value of Column D for the row where the value of the Cell in Column A is 100. And also Column D for that Row may change whereas it's Column A value for that same Row may stay the same.

This means i want to look for changes in Column A. Sometimes 100 might be Row 2, but sometimes it might be Row 302. Also, if Sheet 1, Column A, Value=100 stays in the same Row, but its Column D value changes, I'd want Sheet 2, Cell A2 to reflect that change in Column D for the Row where Value=100. And I want Sheet 2, Cell A2 to reflect whatever Column D is for the row where Column A equals 100.

VLOOKUP only searches column A when properly sorted, but i'd like to be able to search any unsorted column for the matching value.

  • A VLOOKUP or INDEX/MATCH may do the job., It would be a lot easier to follow your explanation with sample data. – cybernetic.nomad Mar 15 at 16:51
0

This finds the first instance of 100 in column A and returns the value from column D of the same row.

=INDIRECT("D"&MATCH(100,A:A,0))

or referencing another sheet:

=INDIRECT("Sheet1!D"&MATCH(100,Sheet1!A:A,0))
  • that looks to be the correct logic. will try and upvote if it works. but what would i add to reference another sheet? – dns dave Mar 15 at 16:51

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.