Sometime in November, I got an interesting E-mail from a gentleman named Guru. The title said “Excel Workshop in Maldives”. In the email, Guru introduced himself and asked me if I can come to Maldives to conduct few Excel workshops for companies and individuals.
I usually neglect such mails as many times the actual training (or big consulting assignment etc.) will not happen. So I replied to him giving my number and asked him to call me. I was surprised to receive his call. After talking awhile, it was clear that Guru is tenacious and would not take No for an answer.
So we set things in motion and thanks to Guru’s perseverance, I ended up boarding a flight to Male on 22nd of January. This is a story of what happened next…,
(note: I traveled with my son & Jo. Business trip for me, beach holiday for them.)
The journey (onward):
Almost all the flights to Male from India leave from either Trivandrum, Chennai or Bangalore. We choose the Trivandrum option because it is a direct flight (other 2 flights have a pit-stop at Colombo, Sri-Lanka). The flight took 75 minutes.
Soon after take-off, all we could see was big-blue Indian Ocean beneath us. About 60 minutes in to the journey and small islands and resorts started appearing. They looked nothing like I have ever imagined. The seas were clear blue or green or a mix. The beaches were pure white. The islands looked lush with greenery. The water villas (houses constructed on water) looked calm and elegant.
The same pattern repeated for next 5-10 minutes in various islands before our pilot announced that we would be touching down at Male international airport.
Male Airport
When I saw Male’s map on Google, I thought the airport could not fit anything bigger than an ATR. But I was surprised when we boarded an Airbus 320 at Trivandrum. But I was in for even bigger surprise when we landed at Male. The airport is as big as many other airports I have seen. In fact, they have an entire island for the airport.
So after getting down and finishing immigration formalities we came out.
A note about visas for Maldives:
Maldives has no prior visa requirements for a majority of countries. Almost anyone can go and get a tourist visa for 30 days. Visit Maldives Visa site for more info.
Guru was waiting for us outside airport. We took a ferry to Male, the capital of Maldives. The airport and capital city are well connected by frequent ferries (one every 10 mins). It takes about 15 mins to reach the city.
Male City – Initial Impressions
I have been to some of the most crowded cities in the world – New York, Mumbai, Hong Kong. But I never saw narrower roads than I did in Male. This is probably the first impression you get too. A majority of Maldivians live in Male. Since the city is a small island, they had to get creative to contain so many people and shops and everything. Some of the impressive ways they manage this:
- Almost all roads are one-ways
- Many buildings are multistory and quite narrow.
- Many elevators are small and can carry 6 people at a time.
- People use bikes or cycles (although you will find a lot of cars, including a BMW that was parked near Sultan Park for the entire duration I was there)
Since I was traveling with family, Guru has arranged accommodation for us in his boss’ house. This was much better than staying in a hotel as my son got more space to play and run.
How my Workshops went?
I had a busy schedule from the moment I landed. On the first day, we conducted a Free Excel Workshop in Aminiya School. This was to get participants to sign-up for our paid workshop.
I choose the topic of Conditional Formatting as it is very close to my heart and the session went very well. We ended up adding few more people to our evening batch.
Later Guru briefed me that I need to conduct 18 hours of Excel training at STELCO (State Electricity Company) and 9 hours at HDC (Hulhumale Development Corp)
We started the training at STELCO next day. We spent the first day discussing Excel overview and writing formulas. The participants were quite friendly and by second day we were cracking jokes and having fun while learning lots of stuff.
Later in the night I conducted a session for individual participants (about 9 of them) again on same topics.
This went for 3 days before we added one more client – FSM (Fuel Supplies Maldives).
What I learned from my workshops?
- Start with Overview: I always assumed that people would know how to use Excel. So my learning plan started with Formulas (that is how it is for Excel School too). But I was surprised to realize that people want to have a good overview of Excel before jumping in to specifics. So after frist day morning, I changed my plan. My first class became “overview of Excel”. In fact, I even added a lesson Zero to Excel School after coming back.
- No plan: Before leaving for Maldives, I made elaborate learning plans for both intermediate and advanced Excel sessions. But after landing there, I realized that it is better to have a loosely structured plan and modify it as per participant’s needs.
- Metaphors are powerful: Often while explaining concepts like namebox, relative vs. absolute references, countif, pivot tables, conditional formatting it was difficult for some participants to understand how they would be relevant. But thankfully, using metaphors I could get my point across
- Talking for 8 hours a day is a lot of work: After talking for more than 8 hours a day for a week, suddenly I respect all my teachers even more.
- Pivot tables excite people: In all my classes, when I demoed pivot tables, I could hear “wow!!! that is so much better” from many participants. They raise the overall curiosity of the class and suddenly everyone is paying attention to know more. (hint: expect more pivot table stuff on chandoo.org too)
Participants’ Response:
We had about 50 people attending the workshops. And a majority of them gave a very high rating (4 or 5 out of 5) for it. Many actually wrote testimonials and praised us for doing it. All 3 companies are hopeful to do a follow-up workshop in a few months.
I also learned a lot of things about Excel while explaining or answering students’ questions.
I had a self-doubt whether I would be able to pull off an in-person training program. Now, I am more confident. I can handle future workshops more easily.
So it was a win-win for all of us.
What we did when I was not teaching Excel?
Despite being a small city, Male has lots of surprises. So we were busy for the first 4 days exploring the city and discovering our way back to home. The best things I liked about Male are,
Walks: You can walk from one end of Male to other end in about 30 mins. So you would start from one ocean front and end up another. Although the streets are narrow, they all have foot-paths. So it is easy to walk, leisurely explore the shops and other attractions, watch other tourists and locals.
Ocean Front near Jumhoore Maidhan: is a very lively place to sit and watch tourists, enjoy the sun, ocean breeze, play in the park (or watch your kids play).
(Nishanth and Jo in the sun – Near Jumhoore Maidhan, Male)
Food: Lots of restaurants serving authentic Asian, continental and Italian varieties. So many varieties of fish and other sea-food at really affordable prices. We especially liked Thai and sea-food at Lemongrass restaurant near Farhadee Magu (close to Sultan Park).
People: Although we did not interact with many people outside my training hours, what I found is that people are very friendly, helpful and cheerful. Participants of my training program are even more awesome as they showed immense curiosity and sense of humor.
About the beaches:
But many people do not go to Maldives to visit Male. They go because of the spectacular beaches in Maldives.
Unfortunately, due to my busy schedule, we could not get much time to explore various beautiful islands in the archipelago. But we did go to two different islands and they both were mind-blowing.
Hulhumale:
(view from Hulhumale jetty)
This is an island close to Male. Government of Maldives is developing this island as the mainland Male is very congested. This is where all the new projects are coming up. (and HDC, one of the companies I did training for, is developing the island)
We went to Hulhumale by a ferry on Wednesday (26th of Jan). Hulhumale has lots of beaches (Male has only ocean fronts and one artificial beach). The beaches are very clean, sand is clear white and you can walk almost 200-300 meters in to the water without getting drowned (in some places). We spent the whole evening there.
Six Sense Resort – Lankanfushi Island
(view from a water villa – Six Sense Resort – Lankanfushi Island)
A couple of the evening batch students worked at Six Sense Soneva Gili Resort in Lankanfushi Island (one was a training manager and another is a F&B manager). Initially, the training manager tried to arrange a similar workshop at the resort. But they could not make a decision immediately. So we agreed that next time I visit Maldives, I will conduct a workshop at the resort.
But they invited us to spend a day at the resort. Since Maldives is an Islamic country and Friday (and for some companies Saturday) are holidays. So we decided visit the island on Friday (28th). Initially I wanted to say no to the proposal as I was too tired with all the classes. But my wife was keen to enjoy the beaches. So we did go.
Going there proved to be the best part of the trip. The island and the beaches there are nothing like I have ever seen. The waters, sun, sky and calm resort instantly rejuvenated me. We spent the whole afternoon at the beach. I even swam for a while.
We had some coffee and snacks the restaurant. My son started crying loudly when the restaurant supervisor, a Japanese lady, said hello to him.
(3 of us at the staff canteen – Six Sense resort)
We left the place barely in time to catch the ferry back to Male.
Are you planning to Visit Maldives? A short tourist guide:
When to go?
November to March is a really good time to visit Maldives. It is very sunny and hot through out the year here. So you may want to avoid the summer months (April-June) or Monsoons (July-October).
What to take?
Beach-wear of course. They have showers in Airport too!!! Carry sun-glasses, hats, slippers, cotton clothes.
About Visas:
For a majority of countries, You do not require visa to enter Maldives. You can get a tourist visa for 30 days upon arrival. Visit Maldives Visa information site.
How much it costs to visit Maldives?
Maldivian currency is Rufiya (MVR). You can get 12.75 MVR for each US $.
Almost all the items are imported to Maldives from near-by countries. For this reason, many food items etc are expensive. That said, compared to costs in many developed countries, Maldives is cheap. You can have a really good meal (with sea-food etc.) for about $10.
Some hacks for budget travelers:
- For breakfast, go to Seahouse at the Hulhumale Ferry Terminal. They have breakfast buffet for 65 MVR on all days. You can find all varieties (English, Continental, US, Asian) of breakfast items, juices etc. The best part is, you can watch the ocean, speedboats, soak in sun while enjoying the food for a couple of hours.
- Do not buy milk: It is very expensive here. Instead, you can buy Milk powder and use it for coffee / tea. You can also get yogurt.
- Take a cab: Taxis are un-metered in Male. You can go from anywhere to anywhere by paying just 20MVR. So if you are tired, hail a cab.
- Eat out: There are tons of places through of Male that are cheap and delicious. You can walk in to almost any restaurant and eat food for less than $20.
- No shopping: Since almost everything are imported, you will find the prices to be on higher side for usual shopping items like consumer electronics, clothes, shoes or cosmetics. I was told TVs are cheaper, but carrying one to back home would be a pain.
Closing Thoughts:
We really enjoyed our brief stay at Maldives. I am thankful to Guru and IIPD (the organization Guru works for) for everything they have done to make the training workshops a great success.
Special thanks to STELCO, HDC and FSM for trusting me and giving their time & attention.
I was left with a few hundred Rufiyah by the time we returned to Airport. But I did not give them back to Guru as I know that I would be visiting Male once again. But next time, I hope I could spend a few more hours by the beach too.
Bonus Excel Tip for those of you making this far:
I know you read the travelogue because you want to know more about me. I find it very humbling. So here is a small Excel tip 🙂
Use NETWORKDAYS.INTL() to calculate working days between 2 days with custom weekends:
Often, you may want to find out number of working days between 2 dates. We can use NETWORKDAYS() formula to do this. For eg. NETWORKDAYS(“1-JAN-2011″,”31-JAN-2011”) would tell you the number of working days in Jan (assuming Saturday and Sunday are weekend holidays).
But what if you live in countries like Maldives, where Friday is the weekend. Well, thankfully, you can use the NETWORKDAYS.INTL() formula. This is a new formula introduced in Excel 2010.
So =NETWORKDAYS.INTL(“1-JAN-2011″,”31-JAN-2011”,16) will give you the number of working days in Jan 2011 assuming Friday is a weekend holiday.
But what if you don’t have Excel 2010?
Well, you can use networkingdays() custom UDF instead.
40 Responses to “Looking up when the data won’t co-operate (case study)”
Nice Trick.. Clever use of cell references
Here is a formula I tried to create:
=SUMPRODUCT(((NOT(ISERROR(SEARCH(L5,B4:H14))))*1),(B5:H15))
It takes care of Caveat #1 (can handle text), but Caveat #2 remains.
In situations like this, I will often use VBA to restructure the data (2 columns: dates and values) on to a new worksheet. I can then use this 'clean' source for data analysis (formula or pivot table).
=SUMPRODUCT(((NOT(ISERROR(SEARCH(L5,B4:H14))))*1),(B5:H15)) and complex formulae in general are all very well but when you come back to them in a few weeks / months time, it is not at all easy to see what they do and what the limitations are.
Hi Chandoo,
I had used this type of cell ref. various times while calculating average.
But for the situation here try below formula . Note this is an array formula and must be confirmed with Ctrl+Shift+Enter.
=SMALL(IF(MMULT((L5=B4:H14)*IF(ISNUMBER(B5:H15),B5:H15),{1;1;1;1;1;1;1}),MMULT((L5=B4:H14)*IF(ISNUMBER(B5:H15),B5:H15),{1;1;1;1;1;1;1})),1)
Regards,
Hi, I think Using SEARCH in here will create a problem say there is a text like SUN and another text SUNLIGHT both result will be added by SUMPRODUCT.
Regards,
Array option.
=SUM(IF(MOD(ROW(B4:B14),2)=MOD(ROW(B4),2),IF(B4:H14=L5,B5:H15)))
Regards
@Elias,
Nice approach.
Although not requested - the formula I suggested closes all possibilities.
Criteria: _____ Value to retrieve:
Date__________ Numeric
Date__________Textual
Textual________Numeric
Textual________Textual
While your formula copes with only the 3 first combinations.
Michael (Micky) Avidan
“Microsoft® Answer” – Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL
@Michael,
Sorry but I don’t understand your point. I believe the challenge was to return the summary of a given date. What is your really volatile formula doing that mine is not?
Regards
@Elias,
I didn't say that the challenge differs from what you just mentioned/aimed to nor that your formula doesn't provide the requested result.
Please read my previous comment again and focus on the last combination (TEXT / TEXT).
I, myself, always try to provide a global Formula that is capable to handle all sorts of data.
Michael (Micky) Avidan
“Microsoft® Answer” – Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL
@Michael,
I see your point, but you are missing the below points if you are trying to cover all sorts of data.
What happened if the lookup value does not exist?
Do you want the first, second, summary, concatenation of the values if the look value is repeated?
See they are too many possibilities to be cover with just one formula.
Regards
1) The range: B4:H15 was named: RNG.
2) The following Array Formula was "retrieved from my sleeve" and I hope it can be shorten.
3) The formula seems to take care of BOTH(!) caveats.
-------------------------------------------------------------------------------
=OFFSET(INDIRECT(ADDRESS(SMALL(IF(RNG=L5,ROW(RNG),""),1),MOD(SMALL(IF(RNG=L5,(ROW(RNG))+COLUMN(RNG)/10),1),1)*10)),1,)
-------------------------------------------------------------------------------
Michael (Micky) Avidan
“Microsoft® Answer” – Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL
@Michael,
Check what happened with your result if you type 41927 in D5.
Regards
Correct. Didn't predict that.
Will find time to work something out.
Michael (Micky) Avidan
“Microsoft® Answer” – Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL
@Elias,
Let's hope the following Array Formula "closes all open doors".
Again - it has nothing to do with your formula which works fine as long as the 3 first mentioned combinations are concerned.
-------------------------------------------------------------------------------
=INDEX(RNG,LARGE(IF(RNG=L5,MOD(ROW(RNG)-1,2)*(ROW(RNG)),""),1)-2,(MOD(SMALL(IF(RNG=L5,(ROW(RNG))+COLUMN(RNG)/10),1),1)*10)-1)
-------------------------------------------------------------------------------
Michael (Micky) Avidan
“Microsoft® Answer” – Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL
Ok, if you insist. The following will cover all the scenarios you listed. However, I’ll never recommend/use such of formula.
Defined names:
rDat = $B$4:$H$15
rRow =ROW(rDat)-MIN(ROW(rDat))+1
rCol =COLUMN(rDat)-MIN(COLUMN(rDat))+1
rInc =MOD(rRow,2)=MOD(MIN(rRow),2)
L6=INDEX(rDat,MAX(IF(rInc,IF(rDat=L5,rRow)))+1,MAX(IF(rInc,IF(rDat=L5,rCol))))
Array Enter
Regards
@Michael,
unfortunately, your array formula still seems to return wrong results (eg 3-Nov).
If data are organized like in the example, ie. looks like a calendar, the INDEX formula seems quite simple:
=INDEX($B$4:$H$15,ROUNDDOWN((L5-B4)/7,0)*2+2,MOD((L5-B4),7)+1)
Yours is effectively the same as what I just came up with, and I believe this is the optimal answer to this particular problem.
My solution, before I saw yours:
=OFFSET(B5,QUOTIENT(L5-B4,7)*2,MOD(L5-B4,7))
OFFSET will work for an arbitrary list size, but INDEX might be easier to read.
QUOTIENT does the round and division in a single step.
If there's an improvement over Elias's solution then I for one can't see it.
Perhaps a non-CSE version which would also mean that only two references (B4:H14 and B5:H15), as opposed to three (B4, B4:B14 and and B5:H15), would require manually amending should the data range change, i.e.:
=SUMPRODUCT((ISEVEN(ROW(B4:H14)-MIN(ROW(B4:H14)))*(B4:H14=L5)*B5:H15))
I suppose we could make it a single, uniform range reference:
=SUMPRODUCT((ISEVEN(ROW(B4:H14)-MIN(ROW(B4:H14)))*(B4:H14=L5)*OFFSET(B4:H14,1,,,)))
which might be more appropriate should we e.g. wish to use a Defined Name for our range, i.e.:
=SUMPRODUCT((ISEVEN(ROW(Rng)-MIN(ROW(Rng)))*(Rng=L5)*OFFSET(Rng,1,,,)))
though whether that compensates for the extra, volatile function call is something to be debated.
Regards
I have tried something and then my Excel workbooks got shut down. Maybe that was too much?
Anyway here is what I've tried:
=SUMPRODUCT(INDEX(B5:H15;IF(ISEVEN(ROW(B5:H15));ROW(B5:B15)-ROW(B5)+1);{1\2\3\4\5\6\7}))
Guess that was wrong? Would this approach work anyway?
Looking forward to learn something from you Excel Experts.
Sorry, I haven't took notice of XOR LX's answer. I guess that's kind of what I was looking for.
@Michael Avidan
As it stands that is not a very rigorous construction.
You say "I, myself, always try to provide a global Formula that is capable to handle all sorts of data", which is a wonderful philosophy, but isn't it at least as important that we ensure that our formulas are independent of the row and column references of the data range in question, so that, should that range change, we do not have to re-work our solution?
What happens with your formula, for example, if RNG is instead re-located one row down, from B4:H15 to B5:H16?
When a formula is reliant upon the addition/subtraction of certain constants within the formula, which themselves are necessarily dependent upon the specific rows/columns in which the data lies at any given time (e.g. the -1 in MOD(ROW(RNG)-1,2)), then that formula is not a very flexible one.
Hence the reason for my choice of a slightly longer construction:
ROW(B4:H14)-MIN(ROW(B4:H14))
which ensures that this part of the calculation is not dependent upon the precise location of the data range within the worksheet, and so will give correct answers even if that range is re-located.
Regards
{=OFFSET(B4,MAX((B4:H15=L5)*ISODD(ROW(1:12))*ROW(1:12)),MAX((B4:H15=L5)*ISODD(ROW(1:12))*COLUMN(A:G))-1)}
Non-array formula:
=INDEX(B4:H15,SUMPRODUCT((B4:H15=L5)*(ROW(B4:H15)-ROW(B4)+1)*ISODD(ROW(B4:H15)-ROW(B4)+1))+1,SUMPRODUCT((B4:H15=L5)*(COLUMN(B4:H15)-COLUMN(B4)+1)*ISODD(ROW(B4:H15)-ROW(B4)+1)))
Using one range (B4:H15), one reference (B4), one lookup value (L5) and no INDIRECT or OFFSET.
My trial with defined names:
DateRange
=$B$4:$H$4,$B$6:$H$6,$B$8:$H$8,$B$10:$H$10,$B$12:$H$12,$B$14:$H$14
Position
=RANK('lookup problem'!$L$5,DateRange,1)
L6
=OFFSET(B4,ROUNDUP(Position/7,0)*2- 1,IF(MOD(Position,7)=0,6,MOD(Position,7)-1))
I'd probably just run with something like:
=SUMPRODUCT((B4:H14=L5)*(MOD(ROW(B4:H14),2)=MOD(ROW(B4),2))*B5:H15)
...which is basically the same as Elias' but without the IFs
The opposite of elegant but it works...
=INDEX(B4:H15,IFERROR(MATCH(L5,B4:B14,0),0)+IFERROR(MATCH(L5,C4:C14,0),0)+IFERROR(MATCH(L5,D4:D14,0),0)+IFERROR(MATCH(L5,E4:E14,0),0)+IFERROR(MATCH(L5,F4:F14,0),0)+IFERROR(MATCH(L5,G4:G14,0),0)+IFERROR(MATCH(L5,H4:H14,0),0)+1,IFERROR(MATCH(L5,B4:H4,0),0)+IFERROR(MATCH(L5,B6:H6,0),0)+IFERROR(MATCH(L5,B8:H8,0),0)+IFERROR(MATCH(L5,B10:H10,0),0)+IFERROR(MATCH(L5,B12:H12,0),0)+IFERROR(MATCH(L5,B14:H14,0),0))
=INDEX(B4:H15,
IFERROR(MATCH(L5,B4:B14,0),0)+
IFERROR(MATCH(L5,C4:C14,0),0)+
IFERROR(MATCH(L5,D4:D14,0),0)+
IFERROR(MATCH(L5,E4:E14,0),0)+
IFERROR(MATCH(L5,F4:F14,0),0)+
IFERROR(MATCH(L5,G4:G14,0),0)+
IFERROR(MATCH(L5,H4:H14,0),0)+1,
IFERROR(MATCH(L5,B4:H4,0),0)+
IFERROR(MATCH(L5,B6:H6,0),0)+
IFERROR(MATCH(L5,B8:H8,0),0)+
IFERROR(MATCH(L5,B10:H10,0),0)+
IFERROR(MATCH(L5,B12:H12,0),0)+
IFERROR(MATCH(L5,B14:H14,0),0))
Named Range
rownum = SUMPRODUCT(('lookup problem'!$B$4:$H$14='lookup problem'!$L$5)*ROW('lookup problem'!$B$4:$H$14)*ISEVEN(ROW('lookup problem'!$B$4:$H$14)))
Formula
=OFFSET($A$1,rownum,MATCH(L5,INDIRECT("$B"&rownum&":$H"&rownum),0))
How about SUM(IF(B4:H14=L5,B5:H15)) with array..it should work
Sorry, Chandoo, you can't find stuff this way in every possible scenario.
What if 2014-10-01 sales would equal 41.927 ? Which is serial number for 2014-10-15 ? SUMIF would fail to retrive correct answer. And your example data suggest that such number is possible in your table.
It's better not to search through dates and numbers at the same time.
If I'd solve a problem like this, it'd reformat table first so I get one column with dates and the other with numbers.
In this case, formula to form date column would be:
=INDIRECT(ADDRESS((INT((ROW()-4)/COUNT($B$4:$H$4))+1)*2+2;MOD(ROW()-4;COUNT($B$4:$H$4))+2;4;1))
and numbers would be the same formula with sight adjustment (+3 instead of +2 at the end of first argument):
=INDIRECT(ADDRESS((INT((ROW()-4)/COUNT($B$4:$H$4))+1)*2+3;MOD(ROW()-4;COUNT($B$4:$H$4))+2;4;1))
And now you got two columns that you can safely use for searching!
Oops, sorry, you actually mentioned that it doesn't work if number=date! I missed that part 🙁
={OFFSET(A1,SUM((B4:H14=L5)*ROW((B4:H14))),SUM((B4:H14=L5)*COLUMN((B4:H14)))-1)}
Works for all data... the solution I got for indirect looks little lengthy
I want to count last 20 records of a person, whose marks is greater than 2 and grade "manager". ....
Assume A1 has got names (James, John...etc...)
A2 "Manager"
A3 "2"
Someone please reply
I want to count last 20 records of a person, whose marks is greater than "2" and grade "Manager"
Assume A1 "geroge" A2 "Michael" A3 "George" etc...name can found anywhere in the rows
B1 "Manager" B2" clerk"
C1 "2" C2, "4"
please reply
Simplest I can come up with. No limitations for either 1 or 2. This does assume dates are an ordered list with 7 per row, and 2 rows per set. Assuming this is always true this will work for an arbitrary long list of dates.
=OFFSET(B5,QUOTIENT($L$5-$B$4,7)*2,MOD($L$5-$B$4,7))
@Marc,
Nice approach - however, as there are no "Negative Dates" - try:
=OFFSET(B5,INT(L5-B4)/7)*2,MOD(L5-B4,7))
——————————————————————————-
Michael (Micky) Avidan
“Microsoft® Answer” – Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL
=OFFSET(B4,ROUNDUP((L5-41911+1)/7,0)*2-1,MOD(L5-41911,7))
B4 has been used as reference cell for OFFSET().
FOR ROWS:
ROUNDUP(....,0) gives the integer value of a division. In case of presence of a remainder, ROUNDUP will add 1 to the Quotient.
As opposed to ROUNDUP(), the INT() or QUOTIENT() functions eliminate the remainder.
41911 = 01-Sept-2014, the first date in the data.
*2 has been used because there are 2 columns per set of data.
/7 has been used because there are 7 columns per set of data.
For columns
MOD(L5-41911,7))
Vijaykumar Shetye,
Panaji, Goa, India
This is how i did it
{=INDEX(B4:H15, MAX((L5=B4:H15)*ROW(B4:H15))-2, MAX((L5=B4:H15)*COLUMN(B4:H15))-1 )}
Here's my solution:
=INDEX(B4:H15,MATCH(1,MMULT(--(B4:H15=L5),TRANSPOSE(COLUMN(B4:H15)^0)),0)+1,MATCH(1,MMULT(TRANSPOSE(--(B4:H15=L5)),ROW(B4:H15)^0),0))
Sorry, forgot to mention Ctrl Shift Enter is needed.