Find criteria and copy and paste

I have a value in Sheet6.(A1). I need to search Sheet5 Column ("BA") for that number or as close to that number as possible, then take the data on the corresponding row in column "BE to BL" and copy it to sheet6 Column Z. For example if Sheet6(A1) is 17, then search Sheet5 Column (BA) for that number or the closest match. 18 appears in cel BA11 in sheet5 column (BA) which is the closest to 17. Then take the data sarting on row 11 column BE:BL and copy to sheet6 column z.
macone1976Incident ResponderAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
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
macone1976Incident ResponderAuthor Commented:
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
Rob HensonFinance AnalystCommented:
Now travelling so unable to look until later this evening, 8:00 GMT at earliest.
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

Rob HensonFinance AnalystCommented:
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
macone1976Incident ResponderAuthor Commented:
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.
Rob HensonFinance AnalystCommented:
That helps but doesn't solve the other issues.
macone1976Incident ResponderAuthor Commented:
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.
Rob HensonFinance AnalystCommented:
Sorry, what is hhZ time??
macone1976Incident ResponderAuthor Commented:
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
Rob HensonFinance AnalystCommented:
Yep, just realised that the data is in 3 hour intervals when you said that it was hhZ time.

Converting to numbers in column Z using VALUE as suggested above gives a result but it returns 6, the result for the last occurence of 15 (row 50), don't fully understand why its skipping all others.

If you change the formula to only include down to row 16 as you suggested above, it returns 22; the correct result if I am not mistaken.

=INDEX(Sheet4!$E$1:$Y$16,MATCH($A$1,Sheet4!$Z$1:$Z$16,1),1)

And you don't need to array enter (Ctrl + Shift + Enter) as it was originally.

If we are done here, its now after 11pm so could do with hitting the hay. By the way, my earlier comment about 8:00, I meant 8pm.

Thanks
Rob H

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
macone1976Incident ResponderAuthor Commented:
Thank you for all your help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.