VLOOKUP with a Boolean membership condition??

  • Thread starter Thread starter Flamikey
  • Start date Start date
F

Flamikey

Hi,
I am trying to modify a VLOOKUP function using a Boolean condition
instead of nesting a bunch of If functions together (and then hitting
the nesting limit of 7). What I would like to do in pseudo code is

IF active cell
is IN (list)
/*I would like this list to behave like standard SQL, i.e. no limit to
the number of variables in list, each item separated by comma*/
Then VLOOKUP(active cell, Range Reference, Column #, false)
Else = 0

Nesting IF statements works until you have more than 7 items in the
list. I would also like to use the same function above with NOT IN
(list) as well. Im not real proficient writing code, but I am amazed
that excel does not have a predefined Is In List function.

Thanks
 
Flamikey,

Not sure I have properly understood your question. Let me rephrase what I
understood. If my understanding is incorrect, then please correct me.

You want a method of being able to get around the 7 if limit to see if the
value of an active cell is in a list?

I would just use the Match and ISNA function.

For example, create a range name for your list of values, say MyList. Then
assuming A1 is the active cell,

=IF(ISNA(MATCH(A1,MyList,0)), 0, VLOOKUP(yadda, yadda, yadda))

Essentially, if match creates an error, then A1 value is not in the list,
and you want 0. Otherwise, A1 is in the list.

Is that what you are looking for?

Please clarify if I have missed the mark.

Best regards,
Kevin
 
Back
Top