Let me explain my best. I hope this help.
9E300;
When you use it in Excel it will changed to 9E+300. It is a scientific
notation of a big number. 300 zeros after 9, so it is like
90000000000000000000…… This will give a big number. As you know LOOKUP will
always look for the number <=Lookup_Value. So here is
<=9000000000000000000……………..
SEARCH({"Sa","Su","Sha"},B4);
This will look for the each array in B4, “Sa”,’Su”,”Sha” & will give the
position (*How many characters after*). For B4 will get like
{1,#VALUE!,#VALUE!}
So here “Sa” contains in starting of B4, rest of the doesn’t. LOOKUP will
ignore the error values.
In these 3 arrays, position of the Lookup_value is 1 (<=9E300, =1) Lookup
will give the corresponding value in result_array, which is from
{"Hero","Boy","Shaan"} = “Hero”
Finally Lookup will like,
LOOKUP(9E+300,{1,#VALUE!,#VALUE!},{"Hero","Boy","Shaan"}) here position of
lookup value is 1, so 1st value from result_vector = Hero
For B5,
LOOKUP(9E+300,{#VALUE!,1,#VALUE!},{"Hero","Boy","Shaan"}) here position of
lookup value is 2, so 2nd value from result_vector = Boy
For B6,
LOOKUP(9E+300,{#VALUE!,#VALUE!,1},{"Hero","Boy","Shaan"}) here position of
lookup value is 3, so 3rd value from result_vector = Shaan
__________
Haseeb
--
FORUM RULES (934+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
