How to use Vlookup with IF statement?

[Jyggalag]

Hi all,

I am very rusty in Vlookup (embarassing!) and I am currently looking at this data:

I would like to create a formula that checks the same date in column A, as checked in column F and then tells me whether or not the value for this date is also present in my data set in columns F-G.

I would cell H2 to return "VALUE IS PRESENT" (and for example for cell H16 to return "VALUE is NOT present", where the values, as visually seen, are different).

I know that I need to create some sort of IF(VLOOKUP) formula, but I am not sure how?

Does anybody know? Or have an alternative better formula?

Would be highly appreciated!!!

Thank you all!

Kind regards,
Jyggalag

 

[Jyggalag]

Yes - that's the point of COUNTIFS.

It would be much simpler if you didn't post pictures of your data and formulas so that we have to retype everything...

=IF(COUNTIFS($A$1:$A$20;F1;$B$1:$B$20;G1)=0;"Not OK";"OK")

Quick update:

I tried it in the real sheet and it returns #VALUE now

Do you know if I made an error?

This is my formula (it links between two excel documents, so a bit long due to that)

=IF(COUNTIFS('[Sheet 3 1865300 (1).xlsx]Sheet1'!$J$32:$J$250;A3;'[Sheet 3 1865300 (1).xlsx]Sheet1'!$L$32:$L$5935;E3)=0;"NOT OK";"OK")

 

[RoryA]

Your ranges aren't the same size. You only go up to row 250 in the first and 5935 in the second.

 

[Jyggalag]

A

Yes - that's the point of COUNTIFS.

It would be much simpler if you didn't post pictures of your data and formulas so that we have to retype everything...

=IF(COUNTIFS($A$1:$A$20;F1;$B$1:$B$20;G1)=0;"Not OK";"OK")

A picture of the error:

And the data it pulls from (column J is dates in the other sheet and column L is the amount):

 

[Jyggalag]

Your ranges aren't the same size. You only go up to row 250 in the first and 5935 in the second.

Embarassing! This fixed it!

Thank you so much Rory! You're my excel-hero :D

 

[Jyggalag]

Hi Rory,

Sorry for disturbing you so many times. I promise this will be the last time

Regarding the formula (which is amazing by the way):

=IF(COUNTIFS('[Sheet 3 1865300 (1).xlsx]Sheet1'!$J$32:$J$250;A82;'[Sheet 3 1865300 (1).xlsx]Sheet1'!$L$32:$L$250;E82)=0;"NOT OK";"OK")

Your ranges aren't the same size. You only go up to row 250 in the first and 5935 in the second.

I actually two columns of dates, and I want to check it for both to see if I get a match

However, adding another criteria wont work, as it will want both of these criteria to be a success (and it's fine if it matches just one date). I imagine I have to use an OR formula incorporated into this, but i'm not sure how. Can you help me out?

I would like to check if there is a match in Column J (dates) OR in column K (also dates) alongside the amount (column L). Column L HAS to match, but it is fine if column J does not match as long as column K does or vice versa

Does that make sense? Would be amazing!