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.