My area is A1:M60. I have 10 sets of 6 rows within that area with only 2 row from each set actually being searched (its ok to search entire area though as there will never be a match in the other rows). I am trying to search the area for the first instance of a reference cell and return the value of the cell that is 2 cells above it. I thought I could do it with an OR() and a series of HLOOKUPs but that didn't work. The result is always "0". Also, it looked really bulky for this function. Here is the function I have now:
=OR(HLOOKUP(LEFT(Y3, 4),$1:$3, 1, FALSE), HLOOKUP(LEFT(Y3, 4),$7:$9, 1, FALSE), HLOOKUP(LEFT(Y3, 4),$13:$15, 1, FALSE), HLOOKUP(LEFT(Y3, 4),$19:$21, 1, FALSE), HLOOKUP(LEFT(Y3, 4),$25:$27, 1, FALSE), HLOOKUP(LEFT(Y3, 4),$31:$33, 1, FALSE), HLOOKUP(LEFT(Y3, 4),$37:$39, 1, FALSE), HLOOKUP(LEFT(Y3, 4),$43:$45, 1, FALSE), HLOOKUP(LEFT(Y3, 4),$49:$51, 1, FALSE))
Any help would be greatly appreciated.
Bookmarks