Google-sheets – Google spreadsheet lookup

google sheets

I need to lookup cell values on multiple sheets (tabs in current sheet) and generate a simple report on a sheet/tab named "REPORT" of the current Google Sheet.

Sheetname1
Lookup column A for X, provide corresponding value in column B on those rows

Sheetname(N)
Lookup column A for X, provide corresponding value in column B on those rows

Simple enough, I know, but I am a novice at Google Sheets functions, and it would take me days to figure it out from other sample formulas…

Best Answer

You can use either filter or query for this.

=filter(Sheetname1!B:B, A:A = X)

=query(Sheetname1!A:B, "select B where A = X")

Related Solutions

Google Sheets – How to Change a Value Within a Function for Each Row

I think you forgot to separate the first part of the url, the formula and the second part with quotations, deeming them as strings, note there are quotes around "http://www.domain.com/sample" then a function insertion then the rest of the string ".json"

=arrayformula("http://www.domain.com/sample"&row(1:100)&".json")

Google-sheets – Highlight/reject cells that contain any of the values from another sheet

Suppose that the wordlist is in Column A of Sheet1, and you want to apply validation to the range B2:D10 of the current sheet.

Method 1: match

Use custom formula

=iserror(match(B2, Sheet1!$A$1:$A$6, 0))

for data validation.

B2 is the upper left corner of the range to which the rule is applied.
The third parameter 0 requires exact match
The formula returns True only if match returns an error, which means no match is found.

Data validation accepts the values for which the formula evaluates to True. Same can be done with conditional formatting.

Method 2: regexmatch

Form a regular expression from the list and validate against that. The string

 "^(" & join("|", filter(Sheet1!$A1:$A, len(Sheet1!$A1:$A))) & ")$"

concatenates all values in that column into a regular expression that will match any of them. (For example, "^(first|second|third)$").

Then you can use regexmatch by this string to validate or conditionally format the data anywhere else. For example:

=not(regexmatch(B2, "^("&join("|", filter(Sheet1!$A1:$A, len(Sheet1!$A1:$A)))&")$"))

which means the content must not match the regular expression.