Find criteria and copy and paste

Is the data in the sheet sorted in order by column BA.

If so, you can use a combination of INDEX and MATCH functions.

The syntax for MATCH is:

=MATCH(LookupValue,LookupAray,LookupType)

LookupValue - in your example, 17, or the cell reference containing the 17
LookupArray - column BA in your example
LookupType - There are three options for the lookup type -1, 0 or 1
  -1 ~ Finds the smallest value that is greater than or equal to LookupValue. Data must be sorted in Descending Order
   0 ~ Finds an exact match to LookupValue
   1 ~ Finds the largest value that is less than or equal to lookup alue. Data must be in Ascending order.

Combined with INDEX, you can look at range BE to BL and find the relevant row with MATCH and return the relevant column:

In column Z, for returning result from column BE =INDEX(Sheet5!$BE:$BL,MATCH(Sheet6!$A$1,Sheet5!$BA:$BA,-1),1)
In column AA, for returning result from column BF =INDEX(Sheet5!$BE:$BL,MATCH(Sheet6!$A$1,Sheet5!$BA:$BA,-1),2)
In column AB, for returning result from column BG =INDEX(Sheet5!$BE:$BL,MATCH(Sheet6!$A$1,Sheet5!$BA:$BA,-1),3)
In column AC, for returning result from column BH =INDEX(Sheet5!$BE:$BL,MATCH(Sheet6!$A$1,Sheet5!$BA:$BA,-1),4)
In column AD, for returning result from column BI =INDEX(Sheet5!$BE:$BL,MATCH(Sheet6!$A$1,Sheet5!$BA:$BA,-1),5)
In column AE, for returning result from column BJ =INDEX(Sheet5!$BE:$BL,MATCH(Sheet6!$A$1,Sheet5!$BA:$BA,-1),6)
In column AF, for returning result from column BK =INDEX(Sheet5!$BE:$BL,MATCH(Sheet6!$A$1,Sheet5!$BA:$BA,-1),7)
In column AG, for returning result from column BL =INDEX(Sheet5!$BE:$BL,MATCH(Sheet6!$A$1,Sheet5!$BA:$BA,-1),8)

You may notice that the only change is the number right at the end, this is the column of the range BE:BL to return. To make things simpler you could put numbers 1 to 8 in a row of cells and then reference the relevant cell in the row, the formula can then be copied across more simply.

Thanks
Rob H

Hi Rob, I could not get it to work.  Attached is a sample of what I am needing.  On sheet 6 I highlighted where the data needs to go. and on sheet 4 what data that needs to be copied.  The value in A1 is 16 so on sheet 4 I would need the value started on the row with 15.  The green highlight is the row that will be evaluated against A1
Book3.xlsx

Now travelling so unable to look until later this evening, 8:00 GMT at earliest.

Right, I have taken a look at the file and I see some issues.

1) Data is not in numerical order - MATCH function suggested relies on data being sorted, ascending or descending depending on requirement for greater than or less than, for close match.

2) Value in Sheet6!A1 is true number, values in column Z are text although they look like numbers.

Options
2a) Make the value in A1 also text by preceding number with apostrophe
2b) Convert values in Z to true numbers, easiest way is to change the formula in Z to =VALUE(LEFT(D2,2) and use custom format 00 to show two digits.

3) Values in Z are not unique, how do you determine which of the close match items to retrieve?

Thanks
Rob H

Rob, thanks for the response.  It is easier to do option 2b, the values in Z are the same numbers as in column D, but I removed the "Z" to make it easier to match.

That helps but doesn't solve the other issues.

I need to match the first instance so in reality from Z5:Z16 would be the search area to match the criteria in A1.  These number are basically hhZ time.

Sorry, what is hhZ time??

Oh sorry the data in Column D (00Z, 03Z, 06Z) is Zulu Time, Universal time.  So I tried to make it easier to search for the data by just extracting the numbers and leaving out the "Z"  The Value in Sheet 6 A1 is also Zulu time without the Z.  So in the example 16 is 16:00 Zulu

Thank you for all your help.