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
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.
© 2024 OneMinuteCode. All rights reserved.