excel lookup function, is there a replacement?

Originally Posted by shekhar1660

You can use sumproduct function.

Like to see that one too

@shekhar1660

You can use sumproduct function

care to show us how?

as for look up why do you want to change it?
you could use index/match,vlookup,hlookup nested if statements
you can reference a range of cells instead
but for boundaries like that lookup is best

and at a pinch you could use choose with an if or two in it(well maybe not if cell could have say 9 in it)

try this
=INDEX({"High","Medium","Low","/"},MATCH(E2,{1,11,13,14},1))

Hi,
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
'/

ah that's cheating you never mentioned you were going to wrap it in choose ! but it's pretty nifty

Originally Posted by martindwilson

ah that's cheating you never mentioned you were going to wrap it in choose ! but it's pretty nifty

However purpose is solved I believe?