What's Excel's Equivlent of SQL's IN[values] 3

[Hillary]

What's excel's equivlent of SQL's IN[value1, value2, value3]?

I'm attempting to have Excel return Y or N in a field depending if the Part Number in cell A2 is included in a Part Number list on another worksheet in the same Excel file. My formula is...

=IIF(A2 IN['Part Vendor WO '!A2:A368],"Y","N")

Right now I just have an invaild formula.

Thanks,

Hillary

 

[bluedragon2]

look at the LOOKUP or VLOOKUP functions.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[Hillary]

Okay...I think I getting closer. Now I have a #N/A being returned. My formula is...

=VLOOKUP(A2,'Part Vendor WO '!A1:A367,1,FALSE)

Any ideas???

Thanks,

Hillary

 

[fredericofonseca]

if(isfalse(VLOOKUP(A2,'Part Vendor WO '!A1:A367,1,FALSE)),"not found",=VLOOKUP(A2,'Part Vendor WO '!A1:A367,1,FALSE))

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[fredericofonseca]

oops.

should be iserror, not isfalse



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[bluedragon2]

You will get an #N/A if the value is not found, or if they are different formats. If you lookup a number (4875) and the table has (4875) but it has been entered as text, you will get the error. As Frederico stated, you can inset error checking, but you may want to look at if you are looking with the correct format also.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[Hillary]

WooHoo! It's working! One more question, I hope this is easy for you...

I have the formula in a cell =IF(ISERROR(VLOOKUP(A2,'Part Vendor WO '!A1:A367,1,FALSE)),"not found",VLOOKUP(A2,'Part Vendor WO '!A1:A367,1,FALSE)) and I want to drag this down 700 cells but I only want the A2 reference to change with each cell (A2, A3, A4, etc...) not the 'Part Vendor WO '!A1:A367 range reference. I draged the formuala down 4 rows and manually changed the range reference ('Part Vendor WO '!A1:A367) back to A1:A367 but when I highlight the block of 4 cells and drag it down, it keeps changing the range reference accordingly.

Any idea how to keep the range refernece static but the A2 live?

Thanks,

Hillary

 

[bluedragon2]

=IF(ISERROR(VLOOKUP(A2,'Part Vendor WO '!$A$1:$A$367,1,FALSE)),"not found",VLOOKUP(A2,'Part Vendor WO '!$A$1:$A$367,1,FALSE))

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[bluedragon2]

Quick lesson:

The $ makes a cell designator absolute. For instance, if you have A1 in a cell and paste it, the A and the 1 will offset according to where you pasted it. Making the A absolute ($A1) will only offset the 1 and leave the A as is. Makeing both absolute ($A$1) will leave both the A and the 1 as is when pasted.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[fredericofonseca]

One start from me

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Hillary]

Me too : ) You two make a great team. Thanks for you help and the lesson!

Hillary

 

[xlbo]

BTW - one further point - if you are working with VLOOKUP, and error checking, you are better off using ISNA rather than ISERROR as vlookup can return a #REF! error if the 3rd argument is bigger than the width of the lookup range - ISNA will work for non matches only.

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question