Vlookup with IF

[JadonR]

I would like to create a formula for cell Y2 that uses a vlookup to lookup the value in column 'A' from the 'Purchasing Units' sheet. If it is greater than 0, then show '1'. If it doesn't match, then show nothing ("").

So in other words:

=IF(Y4>0,1,"")

now I need to replace the 0 with =VLOOKUP(Y2,'Purchasing Units'!A:B,1,FALSE)

Sorry for the poor explanation.

 

[tc88]

I would like to create a formula for cell Y2 that uses a vlookup to lookup the value in column 'A' from the 'Purchasing Units' sheet. If it is greater than 0, then show '1'. If it doesn't match, then show nothing ("").

So in other words:

=IF(Y4>0,1,"")

now I need to replace the 0 with =VLOOKUP(Y2,'Purchasing Units'!A:B,1,FALSE)

Sorry for the poor explanation.

=IF(VLOOKUP(Y2,'Purchasing Units'!A1:A100,1,FALSE)>0,1,"")

Just throwing that out there, not sure if it will work. Change the A1:A100 to your desired range of cells.

 

[JadonR]

Exactly what I needed! Thank you, thank you!

 

[Marcelo Branco]

Hi,

The 1 in the formula is correct or is a typo?

=VLOOKUP(Y2,'Purchasing Units'!A:B,1,FALSE)

This, if founded, returns the lookup-value Y2 .....(?)

M.

-Edit-

I hadnt seen the previous post. If this works...Ok

 

[JadonR]

Thanks for checking Marcelo, but column A is where I want to have the lookup take the values from. However, tc88's formula returns a #N/A error for any values that are not in the vlookup on the 'Purchasing Units' sheet. Can anybody modify his formula to result in an empty cell when there is no match.


I tried the following but to no avail:

=IF(ISNA(VLOOKUP(Y717,'Purchasing Units'!A$3:B$100,1,FALSE)>0,1),"",VLOOKUP(Y717,'Purchasing Units'!A$3:B$100,2,FALSE)>0,1)

 

[Marcelo Branco]

Hi,

Why not use COUNTIF?

=IF(COUNTIF('Purchasing Units'!A$3:A$100,Y2)>0,1,"")

HTH

M.

 

[JadonR]

Hi,

Why not use COUNTIF?

=IF(COUNTIF('Purchasing Units'!A$3:A$100,Y2)>0,1,"")

HTH

M.

Brilliant! Exactly what I was looking for!!!

 

[Marcelo Branco]

Brilliant! Exactly what I was looking for!!!

Glad to help

M.