Hi Shekhar,
Well, the prupose of the formula is, in the end, to lookup a text value
based on an input value. This is best accomplished with a lookup function.
ANY of the lookup functions could be used - LOOKUP, HLOOKUP, VLOOKUP,
CHOOSE, INDEX, etc.
Here is a version that utilizes SUMPRODUCT, but since SUMPRODUCT is a more
general purpose array function, and it performs math on the array (it SUMs
all the values in the array), it has more work to do and is likely a little
slower than a pure lookup function solution:
=CHOOSE(SUMPRODUCT(--(E2>={1,11,13,14})),"High","Medium","Low","/")
Totally irrelevant to your question. but if you wanted to summarize your
data based on the count of High,Medium,Low,/ values you could either use
COUNTIF formulas, or an array formula using FREQUENCY, which counts values
into "bins" (groups by value ranges). Unfortunately, FREQUENCY identifies
numbers that are <= bin values instead of >= as LOOKUP does, but a bit of
math and an INDEX function will have it group values the same way LOOKUP
does:
=INDEX(FREQUENCY($E$2:$E$100+0.000000000000001,{1,11,13,14}),ROW($2:$5))
To enter the above array formula, enter it in a vertical range of 4 cells.
In the 4 cells to the left or right of that formula, type the following
labels:
High
Medium
Low
'/
Asa
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Shekhar Sharma
Sent: Wednesday, May 09, 2012 6:09 AM
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Replacement of Lookup function
Just wondering if there is a replacement for the LOOKUP function, at the
moment I am using =LOOKUP(E2,{1,11,13,14;"High","Medium","Low","/"}) to
return a high medium value to a cell etc. Is there another way of doing this
can anyone tell me, which doesn't use LOOKUP?
Can we do this with sumproduct, if yes then how?
--
Shekhar Sharma
9910010060
"Life consists not in holding good cards but in playing
those you hold well"
"Before you judge another, think of your own last mistake."
--
FORUM RULES (986+ 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
--
FORUM RULES (986+ 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
