Complex LOOKUP and Return Criteria possibly using INDEX/MATCH...

Hi again guys!

Sorry for frequent posts, im nearly sorted apart from a LOOKUP im trying to perform.

This is what im trying to achieve;

I have x2 Worksheets ("FORMULAS" & "DATA")

Im after a formula to look for the 'Time' from Cell E2 in Worksheet 'FORMULAS' (and also use it in Cell E3) and find a match in a Range Defined as "UKDATA" in Worksheet 'DATA'.

This is the part i cant figure out...

Once it finds a match;
If the 5th Row down from the Matched 'Time' has the first character of "J" return the value of the row above (ie, the 4th row down from the Time Match).
If the 5th Row down from the Matched 'Time' does not have a "J" as the first character, then return the 5th row down.

I have Uploaded my simplified Spreadsheet and added Notes to highlight the result and where to find all relevant Lookups/Match's and Data.

Thanks for taking your time, any help is greatly appreciated!

Hi Ali!

This is a problem ive been having as i realise that the time must be exact to be a match. I found this out by changing all times to General and seeing that the numbers were different. Then i had a problem of making sure the Times were rounded to the same decimal places. Glenn help me alot with this and im begining to understand it.

The data in Column E (FORMULAS Worksheet) is imported from a .CSV file so unfortunatly alway a different format from the data in the "DATA" Worksheet.

I can easily Custom format the Times in Column E (FORMULAS Worksheet) but its harder to change the Time format from the "DATA" Worksheet.

I have changed this now, would these Times now be a match? Or i do require more "jiggery-pokery" to make these a match?

Thank you for taking the time!

Originally Posted by AliGW

The other problem is that there are not even numbers of AAAAAAAAAAA rows between times and J rows - there are four in the first and 3 in the second, so your required formula will not work for the second time.

Thats the part im stuck with Ali... I cant adjust this Data so my idea was to create a Forumula that finds the Time match then;

If the 5th Row down from the Matched 'Time' has the first character of "J" return the value of the row above (ie, the 4th row down from the Time Match).
If the 5th Row down from the Matched 'Time' does not have a "J" as the first character, then return the 5th row down.

That way no matter which way around the Cell contains a "J" (4th Row or 5th Row) it will always return what im looking for. Which in this case is the Row which is highlighted
in 'Red' in the "DATA" Worksheet.

Excuse my explination of what im trying to achieve... i get carried away and realise i can be hard to understand!

Thanks for taking a look Ali

Thanks for taking the time Special K!

Unfortunatly when i tried that Formula both Cells returned "7 2000" where i need Cell F2 to return A 1000 (the one above) as the 5th Row starts with a letter "J"?

Yes Ali, thats correct. I think i see what you saying (excuse my lack of Excel wisdom!)

In the "DATA" Worksheet Cell A6 Starts with an A. This will not start with the same value as A so for the purpose of this problem Cell A6 could start with a B.

I could try getting it to work a different way as follows, which may help;


If the 5th Row down from the Matched 'Time' DOESNT start with the character "J" THEN return the value in the 5th Row down (Cell A6 "B 1000" for the 03:45 Time Match)
If the 5th Row down from the Matched 'Time' DOES start with the character "J" as the first character, then return the Row 4th Row down from the Time Match (Cell A15 "7 2000" for the 06:05 Time Match)

Does that make it any easier? Again, appologies for poor explination...

Originally Posted by AliGW

Your times aren't matching: you've changed the format on the main sheet so that it looks the same as the secondary sheet, but they are still 12 hours apart. Are they meant to be AM or PM?

All meant to be PM Ali. To be honest that part i could adjust and make the time Match. Its the other part of the formula that confusses me.

For the purpose of this example all the lookup valuesare a match, so that part works.

Ill upload an edited version.

Originally Posted by Special-K

1) You're doing something wrong, Im getting both A 1000 and 7 2000.

2) Again, your times are still not the SAME value.

FORMULAS times are 03:45PM (15:45) and 06:05PM (18:05).
DATA times are 03:45 and 06:05
If you change the format of the times to decimals you will see they are different. They need to be the same or you WON'T get a match.

Sorry!

Yes this works fine! All the time i wasnt worried about the Time Match part was my downfall! Ill never underestimate the importnace of matching the Time Values again.

This work perfectly, thank you Special-K!

Thankyou Ali for taking the time... i appologies on my explinations of what i was trying to achieve...

Done and done... Ahhh Ali yes, i did think i was lossing it! Thats lack of sleep

Onwards and upwards though!... I do really appreciate all your help. Ive never known a more helpfull, friendly Forum.

Try

=IFERROR(IF(AND(MATCH($E2,DATA!$A$1:$A$100,0),INDEX(LEFT(DATA!$A$1:$A$100,1),MATCH($E2,DATA!$A$1:$A$100,0)+6)="J"),INDEX(DATA!$A$1:$A$100,MATCH($E2,DATA!$A$1:$A$100,0)+5),INDEX(DATA!$A$1:$A$100,MATCH(E2,DATA!$A$1:$A$100,0)+4)),"")

with corrected times !

Thanks John for your time!

This also seems to work perfectly... it was my mistake with the discrepancy of the 'Times' that was preventing me seeing that 'Special-K's' original Formula worked after all.

Thank though for helping me