Menu
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?
289
Combining INDEX and MATCH to Lookup Value in Column and Return Value of Another Column. Apart from that, you can use nested INDEX and MATCH formulas to lookup for a value in a column and get the result of another column in the dataset.
Click the cell where you want to enter a reference to another cell. Type an equals (=) sign in the cell. Click the cell in the same worksheet you want to make a reference to, and the cell name is automatically entered after the equal sign. Press Enter to create the cell reference.
You can use the MATCH() function to check if the values in column A also exist in column B. MATCH() returns the position of a cell in a row or column. The syntax for MATCH() is =MATCH(lookup_value, lookup_array, [match_type]) . Using MATCH, you can look up a value both horizontally and vertically.
Use a different function, like VLOOKUP:
=IF(ISERROR(MATCH(A1,B:B, 0)), "No Match", VLOOKUP(A1,B:C,2,FALSE)) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
