I want to fill in the value by searching a separate sheet.

Sheet 1

key
1
2
3
2
1

said the key.

Seat 2

keyval
1 abc
2xyz

Let's say it has a key and a string you want to fill.

I'd like to put the corresponding string for the key in sheet 2 in the second row of sheet 1

It seems that it can be done using the LOOKUP method.
=LOOKUP(A2, 'Sheet 2'!A:A, 'Sheet 2'!B:B)
Fill in the second column with the formula

keyval
1 abc
2xyz
3xyz
2xyz
1 abc

and for some reason it fills up to a value where key=3 does not exist

Is it possible to leave a value that does not exist blank?

google-spreadsheet

2022-09-30 20:26

1 Answers

I think it will work if you use vlookup.こちら It worked well in this environment.

=vlookup(A2, 'Sheet 2'!A:B,2,FALSE)

I'm concerned about duplication of code, but if you can't find a specific string (this time it's not allowed), you can write it as follows:

=if(isna(vlookup(A2, 'Sheet 2'!A:B,2,FALSE))), "NG", vlookup(A2, 'Sheet 2'!A:B,2,FALSE))

Below is an additional note.
iferror made it more concise.

=iferror(vlookup(A2, 'Sheet 2'!A:B, 2, FALSE), "NG")

Another solution using filter.

=iferror(index(filter('sheet 2'!B:B,'sheet 2'!A:A=A2),1) and "NG")

Simply put, search for the same value as A2 in the 'Sheet 2'!A:A range and return the value in column B of the row found.
You don't need to use it in this question, but I think this method is useful if the columns that search for and return values are not continuous.

2022-09-30 20:26

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656