Excel formula is not working in Google Sheet # I need Help

I am generating attendance sheet in excel 19 in my computer. But when I upload it in Google Sheet then it not works for me. Fore more information please see the attachment.

Thanks in Advance.

Since your problem is with Google Sheets, it would have been MUCH BETTER to have included the link https://drive.google.com/file/d/1ymu...ew?usp=sharing in your posting rather than require others to find it in your workbook. When it comes to Google Sheets, better to ignore the yellow box at the top of the page on this site and provide direct links to Google Sheets documents. Point: we need to load this in Google Sheets rather than Excel, and it's beyond a waste of time, bandwidth, local storage to have to download attached files from here, then upload them to our own Google Drives, open them there, and (best) save as Google Sheets.

The problem is that Excel handles LOOKUP differently than Google Sheets does. That is, LOOKUP(2,1/(SIMPLE_array_expression),range) evaluates correctly in Excel without array formula entry, but needs to be ARRAYFORMULA(LOOKUP(2,1/(SIMPLE_array_expression),range)) or LOOKUP(2,INDEX(1/(SIMPLE_array_expression),0),range) in Google Sheets. By SIMPLE_array_expression I mean an array-valued expression not calling IF, TRANSPOSE, MMULT, or MINVERSE functions, all of which require array formula entry to work correctly. In other words, Excel has no problem processing the 2nd argument of LOOKUP(2,1/(one_range=x)/(another_range=y),yet_another_range) without array formula entry, but Google Sheets does. Wrapping the LOOKUP call (or the entire formula) inside ARRAYFORMULA or wrapping such array arguments to LOOKUP inside INDEX(...,0) are necessary in Google Sheets.

That said, I'm not willing to bet that Google Sheet's LOOKUP function matches Excel's binary search semantics. That is, that LOOKUP(2,1/(some_range=x),another_range) would match 2 to the last (bottommost or rightmost) nonerror value in the boolean array and return the corresponding value from another_range. I figure it'd be FAR SAFER to use index(filter(another_range,some_range="x"),countif(some_range,"x")).

I can sympathize with Google. MSFT has never documented this behavior from LOOKUP, definitely not in Excel Help, and if there's any mention of this in MSFT's OOXML ISO standard specs, I haven't been able to find it. Even if Excel's LOOKUP function has worked this way since Excel version 1 back in 1985, Google can't be faulted for refusing to mimic all Excel's undocumented features and semantics.