3

I've this Google Spreadsheet: https://docs.google.com/spreadsheets/d/1-QV49oKCf58HgLBgC8gJ3gc6IXJZAo2xvh6vPTjY05o/edit?usp=sharing

enter image description here

  • check the row in Sheet1 where the domain in Sheet2 is contained
  • return in Sheet2 the related values from the field USER and IP from that row in Sheet1

So for example Sheet2!B3 and Sheet2!B4 should return user1 and pass1. How to do this?

  • 1
    Please try to make your question self-contained. Linking to a sample spreadsheet is great to supplement the information, but it would be better if all the information needed was in your question. That link will eventually stop working or, as in my case, Google Drive may not be reachable. – ale May 27 '16 at 15:50
  • Your link goes to a different sheet. – Karl_S Feb 22 '17 at 18:26
1

The Arrayformula can keep you from copying the formula down. For your use, you want to use vlookup to find and return the value. You will only be able to return the values for one matched domain, so I will assume this is coming from column H in your images.

The simple version places a formula in each column, starting with the USERNAME, place this in cell B2 and make sure cells B3 to the last cell in the column are empty:

=ARRAYFORMULA( IF(ISBLANK(H2:H),,VLOOKUP(H2:H,Sheet2!A2:C,3,FALSE)))

This breaks down as follows. The IF(ISBLANK(H2:H),, takes care of empty rows, returning no value if cell H in a given row. Note that this is very different from returning "" such as IF(ISBLANK(H2:H),"", which in Google Sheets actually returns a value of nothing. The VLOOKUP() looks in Sheet2, columns A through C for a value from column H. If one is found it returns the value from the third column. You could do a similar function in cell C2, making a couple minor changes.

If you want to get REALLY tricky, you can combine both of these and place the formlua in cell B1, making header entries and populating the columns with one formula:

=ARRAYFORMULA(IF(ROW(A1:A) = 1, {"USERNAME", "PASSWORD"}, IF(ISBLANK(H1:H),, {VLOOKUP(H1:H, Sheet2!A2:C, 3), FALSE), VLOOKUP(H1:H, Sheet2!A2:D, 4, FALSE)})))

This first checks to see if the arrayformula is working in row 1. If it is, it returns an array of the headers via {"USERNAME", "PASSWORD"} The curly brackets define an array. The second set of curly brackets returns an array of the two VLOOKUP formulas.

Make sure there is NOTHING else in any of the cells in columns B or C.

0

One approach is to put the following in cell Sheet2!B2, dragging it down the column.

=iferror(indirect("Sheet1!A" & min(arrayformula(if(Sheet1!A$2:J = A2, row(Sheet1!A$2:J), )))), "no match")

It will return the first entry in Sheet1's column A for which the row contains a value in Sheet2!A2. If no match, it returns "no match".

Same can be done for User column, by replacing the first argument of indirect by "Sheet1!B".

Explanation: arrayformula transforms the array of values Sheet1!A$2:J by replacing each entry with the row number, if it matches the given string, or leaves blank if it doesn't.

Then min finds the minimum of all these values. The result is the row number, which can be used in indirect command to retrieve any desired entry from that row. If there is no match, the minimum of an empty range will be 0, which throws an error (there is no cell A0). The error is intercepted by iferror, which displays a user-friendly message.

  • if I try to put in Sheet2!B2 it gives me an error. Thank you – fasttrackx May 28 '16 at 10:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.