[Solved] Search column from specific cell & copy the result

Discuss the spreadsheet application
Post Reply
manosdet
Posts: 18
Joined: Wed Oct 02, 2019 5:41 pm

[Solved] Search column from specific cell & copy the result

Post by manosdet »

Hi,
I am working on a calc file that actually is a pricelist file.
Each line is one product with sku, description, shop category, retail price, wholesale price etc etc.
I want to have at first line one cell to use as search box that will searching 1st column (sku code) and after search will copy the result line to 2nd line.
There are more than 4000 products in file.

I have no idea if this is possible and where to start thinkng about it.
At first should i use search, find or else function?

Thank you in advance for any help
Last edited by manosdet on Thu Apr 08, 2021 7:30 am, edited 1 time in total.
Windows 7/ OpenOffice 4.1.7
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Search column from specific cell and copy the result lin

Post by FJCC »

This can be done with a set of VLOOKUP functions, one for each column. The first parameter in VLOOKUP would be the address of the "search cell" containing the SKU to be found. The second parameter would be the range of cells containing the data. The third parameter would be the column from which to return data. To get data from the second column, enter 2 as the third parameter. The last parameter should be zero. For example

Code: Select all

=VLOOKUP($A$1; $A$3:$F$4000; 2;0)

might be the formula in the cell B2 if A1 holds the SKU you want to search for.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
manosdet
Posts: 18
Joined: Wed Oct 02, 2019 5:41 pm

Re: Search column from specific cell and copy the result lin

Post by manosdet »

frouta_29.ods
(26.34 KiB) Downloaded 126 times
FJCC wrote:This can be done with a set of VLOOKUP functions, one for each column. The first parameter in VLOOKUP would be the address of the "search cell" containing the SKU to be found. The second parameter would be the range of cells containing the data. The third parameter would be the column from which to return data. To get data from the second column, enter 2 as the third parameter. The last parameter should be zero. For example

Code: Select all

=VLOOKUP($A$1; $A$3:$F$4000; 2;0)

might be the formula in the cell B2 if A1 holds the SKU you want to search for.
First of all i want to thank you for your reply.
Ok, let's say that we have that calc file that is similar to mine but with less products.
Can you give me an example on real how to use the set of VLOOKUP functions?
Windows 7/ OpenOffice 4.1.7
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Search column from specific cell and copy the result lin

Post by FJCC »

Notice the formula changes in each column of row 2.
Attachments
frouta_fjcc.ods
(17.68 KiB) Downloaded 153 times
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
manosdet
Posts: 18
Joined: Wed Oct 02, 2019 5:41 pm

Re: Search column from specific cell and copy the result lin

Post by manosdet »

wow Thank you so much FJCC !!!
Is it possible to search the SKU by 4 digit?
ie instead of searching for CA29-3226AS the search term will be 3226.
All the 4 digits on 1st column are unique

I found a solution,
I added one more column with 4 digit codes and use it for search.
Thank you again FJCC
Windows 7/ OpenOffice 4.1.7
arthurserafin
Posts: 1
Joined: Fri Apr 09, 2021 11:42 am

Re: [Solved]Search column from specific cell and copy the re

Post by arthurserafin »

I have a basic tutorial for this one. please see link https://youtu.be/_RpKxdtOQ6s

thank you
Last edited by thomasjk on Fri Apr 09, 2021 8:25 pm, edited 1 time in total.
Reason: Disable live link
openoffice v4.1.7 windows 10 home
Post Reply