• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extract a value from an array of cells

O

obsrvr

New Member
Dear members,

I have a problem - I am trying to extract a value from a range of cells and I would like you to help me with the formula. I want to take the range of 5 cells (see the attached spreadsheet) in which only one has a value (and others do not) and extract that value into another cell with a formula. It is quite cumbersome and prone to mistake to do this task manually.

Thanks much in advance.
obsrvr
 

Attachments

  • Value Extract.xlsx
    9.5 KB · Views: 10
Xiq said:
How about this:
Code: 
=INDEX(A2:E2,MATCH(">"&0,A2:E2,-1))
Click to expand...
This is lovely! it works great, I m so happy! I wish I knew what it does :) What would you recommend to study to be able to come up with such solutions? :) Thanks
 
obsrvr said:
Thanks! I am looking at the difference and I wonder why they are producing the same results...
Click to expand...

Xiq's solution is using "-1" as the match_type argument in the MATCH function. This means that it will look for anything greater than lookup_value. The lookup value can therefore be anything considered to be smaller than the B,C,D,E you are looking for (so any string starting with A, or a number or symbol should work, for your example file).

I'm not really sure why Xiq has used ">"&0. This just results in the string ">0", which works (as I have described above), but is a bit misleading. ">0" might be used in a COUNTIF formula to specify values greater than 0, but in this case the ">" symbol just being a symbol, not an operator.

Meanwhile, my solution uses "0" as the match_type argument in the MATCH function. This means that the function will look for an exact match, BUT allows you to use wilcards in the lookup_value. I have used "*" which just means "anything".

Hope that makes sense.
 
Just an extra note: The above solutions will all work if you have only one value in each row, with the other columns being empty. If you might have more than one value per row, they will behave differently. So you will need to consider what you would like the result to be in these cases.
 
Qwerty_uk said:
... I'm not really sure why Xiq has used ">"&0. This just results in the string ">0", which works (as I have described above), but is a bit misleading. ">0" might be used in a COUNTIF formula to specify values greater than 0, but in this case the ">" symbol just being a symbol, not an operator....
Click to expand...

I guess you are right, because ">0" is seen as a lower text-string value than the letters used in the example, it worked just fine. I could have used any text-string with a lower value to keep it working.
 
Wonderful discussion - thank you for all of your inputs into this.
Qwerty_uk said:
Just an extra note: The above solutions will all work if you have only one value in each row, with the other columns being empty. If you might have more than one value per row, they will behave differently. So you will need to consider what you would like the result to be in these cases.
Click to expand...
- What if I need to extract every 3rd value from a given range?

Also a comment to Narayan/xlstime - what's "zzzzz"? Why does it work? Not to offend anyone, but it seems to be the simplest way...admittedly the other solutions are not cumbersome at all :)
 
Hi ,

zzzzz is used as a wildcard for the largest alphabetic value possible , especially if this is not going to occur in your data range.

In case your data is numeric the wildcard to be used would be a very large number such as 99999999999 ; you can also use expressions such as 9^9 !

Regarding your requirement of returning the 3rd value , can you post some data , both the input data and the expected output , so that things are clear.

Narayan
 
You must log in or register to reply here.
Back
Top