0

So here is the problem: I have 3 lists (adjacent columns) in a spreadsheet.

  1. The 1st list contains the values I am searching for
  2. The 2nd list contains the first half of table array which I am searching
  3. The 3rd list contains the second half of the table array which I am searching

What I would like to do is search the second list for the values in the first list (this is a normal VLOOKUP so far) but instead of returning the values in the third list I need to change them to either "1" or "0".

Can someone please point me in the right direction? Any help is greatly appreciated.

Improve this question
0

1 Answer 1

Reset to default
1

In column 3 you can use this -

=IF(ISNA(IF(MATCH(B1,A:A,0),1)),0,1)

It returns 1 if the value is found, 0 if it isn't.

Or, if you'd rather be smart about it, you can use Engineer Toast's formula

=IFERROR(1+0*MATCH(B1,A:A,0),0)

Essentially, the basis is that by using match, you are checking for the value in the list and return an error if it isn't found.

Improve this answer
4
  • I like =IFERROR(1+0*MATCH(B1,A:A,0),0) only because it uses less functions and is shorter.
    – Engineer Toast
    Feb 19, 2015 at 17:22
  • It probably works better, I just ended up starting with the wrong function, taking me down the path I went.
    – Raystafarian
    Feb 19, 2015 at 17:24
  • Thanks very much for the help guys this works perfectly! :)
    – Good ol' Gin
    Feb 19, 2015 at 17:24
  • 1
    You could also use this: =1-ISNA(MATCH(B1,A:A,0))....or =(COUNTIF(A:A,B1)>0)+0
    – barry houdini
    Feb 20, 2015 at 21:24

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .