Search an area for a matching value and return the data from 2 cells above

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.

If we take the first part of your formula, i.e.:

HLOOKUP(LEFT(Y3, 4),$1:$3, 1, FALSE)

this will look at row 1 to see if there is a match with the first 4 characters of Y3, and if so it will return the corresponding value from the same row (because of the 1 parameter). If you set this to 3 it would return the corresponding value from row 3, i.e. two rows BELOW the row that you are searching. From your description, it would appear that you want to do the opposite, i.e. search through row 3 and return a value from row 1 if there is a match, but HLOOKUP does not work that way. The same comments apply to your other HLOOKUPs.

I'm not sure what your HLOOKUP is meant to return (text or numbers), but the OR function will return the equivalent of TRUE or FALSE, so I don't think that is what you want to do either.

Perhaps you should think about using COUNTIFS to see if the value exists in row 3, like this:

=IF(COUNTIFS($3:$3,LEFT(Y3,4)), … ,"")

where … is some other function to return the appropriate value if there is a match, probably based on INDEX/MATCH, but as I'm not sure exactly what you want to achieve it is difficult to advise. It would help if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon (Attachments button), as this does not work on this forum.

Hope this helps.

Pete

I tried it, but still got a #NUM! error. I am not very familiar with INDEX and have never used aggregate, so I will have to look into those more to trouble shoot. I will post the file at the end of this discussion and let you guys see what I am trying to do.

I think you forgot the attachment.

Pete