Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.5k views
in Technique[技术] by (71.8m points)

excel - Check if Cell value exists in Column, and then get the value of the NEXT Cell

After checking if a cell value exists in a column, I need to get the value of the cell next to the matching cell. For instance, I check if the value in cell A1 exists in column B, and assuming it matches B5, then I want the value in cell C5.

To solve the first half of the problem, I did this...

=IF(ISERROR(MATCH(A1,B:B, 0)), "No Match", "Match")

...and it worked. Then, thanks to an earlier answer on SO, I was also able to obtain the row number of the matching cell:

=IF(ISERROR(MATCH(A1,B:B, 0)), "No Match", "Match on Row " & MATCH(A1,B:B, 0))

So naturally, to get the value of the next cell, I tried...

=IF(ISERROR(MATCH(A1,B:B, 0)), "No Match", C&MATCH(A1,B:B, 0))

...and it doesn't work.

What am I missing? How do I append the column number to the row number returned to achieve the desired result?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

Use a different function, like VLOOKUP:

=IF(ISERROR(MATCH(A1,B:B, 0)), "No Match", VLOOKUP(A1,B:C,2,FALSE))

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...