I am trying to do a calculation based on a range that needs to be identified via a partial match. I've tried many options, including the following function:
{=MEDIAN(INDEX($C$2:$C$6,MATCH(CONCATENATE("*",$E2,"*"),$B$2:$B$6,0)))}
However, this does not give me the correct answer, since it merely takes the first occurance of the match. How can I create a function that will identify a range? I have attached a sample spreadsheet.
F2:
=MEDIAN(IF(ISNUMBER(SEARCH(E2,$B$2:$B$6)),$C$2:$C$6,"X"))
This is an array formula confirmed with Ctrl+Shift+Enter. Then copy downwards.
Median coerces the False evaluations into 0, so outputs of X are ignored.
Make Mom proud: Add to my reputation if I helped out!
Make the Moderators happy: Mark the Thread as Solved if your question was answered!
if i get it
{=MEDIAN($C$2:INDEX($C$2:$C$6,MATCH(CONCATENATE("*",$E2,"*"),$B$2:$B$6,0)))}
Thank you very much! The formula works like a dream.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks