excelexcel-formula

Excel: How to check if a value that meets the specified criteria exists in a lookup table?

Just say I have the following data in Sheet2:

  --+-----+-----+
    :  A  :  B  :
  --+-----+-----+
  1 : abc : 123 :
  --+-----+-----+
  2 : def : 456 :
  --+-----+-----+
  3 : hij : 789 :
  --+-----+-----+

Now, from Sheet1, I want to search for "def" value in Sheet2 which has value "456" in its B column. If it is found, then return "exist" value in the current cell of Sheet1.

How can I do that? I think, I could be achieved by combining the VLOOKUP and ISNA with another function, but I don't know which one.

Solution

  • Using Countifs

    =IF(COUNTIFS(Sheet2!A:A,"def",Sheet2!B:B,456),"exists","")