| View previous topic :: View next topic |
| Author |
Message |
damiengolding
General User
Joined: 17 May 2012
Posts: 5
Location: Osaka, Japan
|
 Posted: Thu May 17, 2012 6:42 pm Post subject: How to search for cell data from a list and output exact m. |
 |
|
Hi, I want to create a function which will search for the value of a cell in each cell of a list.
If the value is found, I want the value to the column to the right to be returned in an output sheet with exactly the same format.
This will be done for each cell in the "Template" sheet.
I am not familiar with spreadsheet functions, so any help would be much appreciated.
I can assume it will use a SEARCH function and IF it finds an exact match it will copy that to the Output sheet., but I am not familiar with which functions to use.
Please can anybody help. Any help at all would be much appreciated.
Thanks
I have attached images below of each sheet.
EXAMPLE (using images attached):
- Search for sleipnir_mobile_for_android from Template sheet(A34) in Data sheet.
- Should find sleipnir_mobile_for_android in A8.
- Then should return B8 in A34 in Output Sheet (if no match should use sleipnir_mobile_for_android)
Data sheet:
Output sheet:
Template sheet:
_________________
Translator and English teacher in Japan.
Please check out my blog. |
|
| Back to top |
|
 |
ozzie
OOo Advocate
Joined: 29 Jul 2010
Posts: 330
Location: victoria
|
| Posted: Thu May 17, 2012 7:26 pm Post subject: |
|
|
If I understand you correctly I think this is what you want
In cell A34 of Output sheet dragged down and across as necessary
| Code: | | =INDEX($Data.$B$1:$B$45;MATCH($Template.A34;$Data.$A$1:$A$45;0)) |
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button). |
|
| Back to top |
|
|
damiengolding
General User
Joined: 17 May 2012
Posts: 5
Location: Osaka, Japan
|
| Posted: Thu May 17, 2012 7:31 pm Post subject: |
|
|
Thanks so much!
That is probably the best reply I have ever received from a forum. Sorry for being such a newbie and not trying myself much to solve it myself. I have some experience with coding, but never understood spreadsheet functions. I wish I could make it up to you.
I have just one question. I have never seen $ used in functions, what do they represent?
Thanks
_________________
Translator and English teacher in Japan.
Please check out my blog. |
|
| Back to top |
|
|
ozzie
OOo Advocate
Joined: 29 Jul 2010
Posts: 330
Location: victoria
|
| Posted: Thu May 17, 2012 7:43 pm Post subject: |
|
|
oops forgot about your last point
| Code: | | =IF(ISNA(INDEX($Data.$B$1:$B$45;MATCH($Template.A34;$Data.$A$1:$A$45;0)));Template.A34;INDEX($Data.$B$1:$B$45;MATCH($Template.A34;$Data.$A$1:$A$45;0))) |
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button). |
|
| Back to top |
|
|
ozzie
OOo Advocate
Joined: 29 Jul 2010
Posts: 330
Location: victoria
|
| Posted: Thu May 17, 2012 7:49 pm Post subject: |
|
|
oops again hadn't seen your reply
The $ locks the reference its attached (either the row,column or sheet name) to so that it wont change when dragged to different cells.
You will note in the formula that "Template.A34" is not locked and that this refernce changes as the formula is moved
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button). |
|
| Back to top |
|
|
pitonyak
Administrator
Joined: 09 Mar 2004
Posts: 3622
Location: Columbus, Ohio, USA
|
| Posted: Thu May 17, 2012 8:05 pm Post subject: |
|
|
| damiengolding wrote: | Thanks so much!
That is probably the best reply I have ever received from a forum. Sorry for being such a newbie and not trying myself much to solve it myself. I have some experience with coding, but never understood spreadsheet functions. I wish I could make it up to you.
I have just one question. I have never seen $ used in functions, what do they represent?
Thanks |
Ozzie is certainly a brilliant one..... 
Welcome to the forum. Learning takes time and effort. The forums help much. You may also find useful information here:
http://www.odfauthors.org/
_________________
--
Andrew Pitonyak
http://www.pitonyak.org/oo.php |
|
| Back to top |
|
|
damiengolding
General User
Joined: 17 May 2012
Posts: 5
Location: Osaka, Japan
|
| Posted: Tue May 29, 2012 11:47 pm Post subject: |
|
|
| ozzie wrote: | oops again hadn't seen your reply
The $ locks the reference its attached (either the row,column or sheet name) to so that it wont change when dragged to different cells.
You will note in the formula that "Template.A34" is not locked and that this refernce changes as the formula is moved |
Thank you very much.
I will try it out and let you know if there are any problems.
_________________
Translator and English teacher in Japan.
Please check out my blog. |
|
| Back to top |
|
|
|
