英文:
How to change the return value in Excel to the content of a specific cell?
问题
=如果(计数(Sheet2!L:L;B6 & "," & C6) > 0; Sheet2!K:K; "")
英文:
=IF(COUNTIF(Sheet2!L:L;B6 & "," & C6) > 0; "x"; "")
Columns B and C contains last and first name of employees (B6 = Cook, C6 = Jim).
In row L of Sheet2 is the same content, but its in one single row divided by a comma (eq. Cook, Jim).
The formula compares the 2 sheets until it finds a match. Now I want the formula the return the value of a cell in the same line where the match was found. The cell contains the employees ID and is located in row K.
For example if the name from B6:C6 is found in Sheet2 in row L28, then the formula should return the employees ID from K28 and if B6:C6 is found in row L200 then the return value is K200.
答案1
得分: 2
Here is the translated content:
简单的 VLOOKUP: 使用连接字符串应该可以工作：
=IFERROR(VLOOKUP(A2&","&B2,Sheet2!A:B,2,FALSE),"未找到")
结果：
可以使用 TRIM: trim 或 TRIM 和 CLEAN 的组合：clean，以避免由于前导、尾随空格和不可打印字符引起的错误：
=IFERROR(VALUE(VLOOKUP(CLEAN(TRIM(A2))&","&CLEAN(TRIM(B2)),CLEAN(TRIM(Sheet2!$A$1:$B$4)),2,FALSE),"未找到")
请注意，在此情况下使用了数据的确切范围 Sheet2 ($A$1:$B$4 而不是 A:B)，以避免减慢 Excel 的速度。
英文:
Simple VLOOKUP: vlookup with concatenated string should work:
=IFERROR(VLOOKUP(A2&","&B2,Sheet2!A:B,2,FALSE),"Nor found")
Result:
Could use TRIM: trim or combination of TRIM and CLEAN: clean in addition to avoid errors caused by leading, trailing spaces and unprintable characters:
=IFERROR(VALUE(VLOOKUP(CLEAN(TRIM(A2))&","&CLEAN(TRIM(B2)),CLEAN(TRIM(Sheet2!$A$1:$B$4)),2,FALSE)),"Not found")
Note that exact range of data in Sheet2 ($A$1:$B$4 instead of A:B) is used in this case to not slow Excel down.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论