if+index+match

kelvin_9

Active Member
Joined
Mar 6, 2015
Messages
444
Office Version
  1. 2019
hi all, any idea on what's wrong with my formula below, i cant return Khloe Kung result same as others user with same formula.?
what should i amend and fix my issue?

thanks all

35e9882ee0
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Change the data in Khloe's cell so it reads 08:00-18:00.
Does this cure the problem?
If so then its because Khloe's cell has two time durations (as does Lodkin's) and Ada's only has one time duration
 
Upvote 0
Change the data in Khloe's cell so it reads 08:00-18:00.
Does this cure the problem?
If so then its because Khloe's cell has two time durations (as does Lodkin's) and Ada's only has one time duration

this may not cure?
i have others employee which has 2-3 more duration instead
i dont know where is/are my problem. :confused:

my formula is used to take the first and the last time if more than 2 duration encountered.
for instance, 09:15-11:00, 11:00-15:00, 15:00-18:30, i return 09:15-18:30

ps. khloe is new hire in this week and those formulas working fine before it hired.

thank you
 
Last edited:
Upvote 0
Images are of little use in this aspect.
Can you post your formula?

Code:
=IF(INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A51,SCHEDULE!$A:$A,0))="","",TEXT(MIN(IFERROR(TIMEVALUE(LEFT(INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A51,SCHEDULE!$A:$A,0)):INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A52,SCHEDULE!$A:$A,0)-1),5)),1)),"hh:mm")&" - "&TEXT(MAX(IFERROR(TIMEVALUE(MID(INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A51,SCHEDULE!$A:$A,0)):INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A52,SCHEDULE!$A:$A,0)-1),7,5)),0)),"hh:mm"))
ps. ctrl+shift+enter

thank you sir
 
Upvote 0
Is it cos of the order of the names?
Your formula refers to the next name down A53 A54 etc

There is no name below Khloe's on the output.

Put the names int he same order.
I suspect Khloe's will correct itself but then Loaklin's will go wrong.
 
Upvote 0
Is it cos of the order of the names?
Your formula refers to the next name down A53 A54 etc

There is no name below Khloe's on the output.

Put the names int he same order.
I suspect Khloe's will correct itself but then Loaklin's will go wrong.

wooo~~
any ideas to avoid similar like this but still can return a correct one?
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top