Find rows in another sheet based on string and copy values?

English
#1

Ok, did some fast search in the documentation but could not find it in the 500 pages + since I honestly dont know what to search for.
I am kind of new to this but I have a sheet with many rows and columns, lets call it sheet1.

What I would like to manage is to find rows in sheet1 based on string and then copy the values to current sheet.
For example sheet1 looks like this -

A           B             C            D    
Volvo       4             1            1
Volvo       3             2            1
Volvo       2             2            1
Fiat        10            10          10
Fiat        2             2            2
Fiat        3             0             5
BMW         4             4            4

Now how do I find rows/columns containing the word “Volvo” and copy the value from for example b, c and d columns? What is the correct way to do this? =SEARCH(“Volvo”,$sheet1) but what else?
Any help appreciated!

1 Like
#2

VLOOKUP() ??

#3

Hi. I am not sure exactly what you want to do. Vlookup function will return the individual cells. Sumif function can return the total for each car for each column. Others are likely to have better solutions but the attached spreadsheet might help you get started. Cheers, Al CarLookup.ods

#4

Thank you for replying and helping me out. Now I know that vlookup can be used and its purpose. Thanks! However what I don’t get is how to search for a particular string and not use columns and rows?
For example I can not use “a2” since a2 will constantly change to other values during time.

Is there a way to do it more dynamic? Like search for a string eg. “Volvo” and then retrieve corresponding array of cells?

#5

Yup. Just replace A2 with “Volvo” keeping the quotation marks. Or put a different word in cell A2. I used A2 to refer to the cell with the word Volvo to reduce the number of times I have to spell it with the increased risk of misspelling, and to make it easier to change the formula to look for another name.
It is too easy using formulae to look up an array to change the array range when drag copying so I made it refer to absolute address by putting $ in front of the column ref and the row ref. Cycle through with F4 to get combination you want. Cheers, Al

#6

M8 all works fine! Thank you. Did not understand it was returning as an array and the use of 0 (false) at the end of function. This is some powerful stuff indeed!
Need to take a deep dive into functions, a lot of water so swim into!
Cheers!

#7

Whoops! It was sloppy of me to say array, I meant data range, it was late. All the best

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.