CODEWITHSUNDEEP
excel
Astaroth
Astaroth

Reputation: 2291

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.

Upvotes: 0

Views: 1360

Answers (2)

chuff
chuff

Reputation: 5876

Using MATCH:

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

If a match cannot be found, this formula will return "#N/A". If you wish it to show something else when there is not a match, then wrap the formula in the IFERROR function.

  =IFERROR(IF(MATCH("def",Sheet2!A:A,0)=MATCH("456",Sheet2!B:B,0),"exists"),"value_to_return")

Upvotes: 0

chris neilsen
chris neilsen

Reputation: 53126

Using Countifs

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

Upvotes: 2

Related Questions