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]

UPDATE:

I think I may have cracked it, but I am facing this issue now:

Cell H11 SHOULD return "OK" given that the value 2130,78 is clearly present in the data set in columns A-B.

However, given that the same date (10-01-2022) presents itself multiple times, the formula seems to get confused and returns only the first value.

Is there a nice fix to this? Either by updating the formula, or changing it?

Thank you all!

 

[Jyggalag]

UPDATE** Sorry for the third message.

I believe an IndexMatch might be the best formula here? Not sure how to do it correctly though, if anybody knows?

 

[RoryA]

That's how VLOOKUP (and INDEX/MATCH) works. I suspect you want something like COUNTIFS instead.

 

[Jyggalag]

That's how VLOOKUP (and INDEX/MATCH) works. I suspect you want something like COUNTIFS instead.

Yes that sounds about right

I used a IF(COUNTIF(Array>0;"OK","NOT OK")) formula before, and while it works, the issue is that some dates have the same value.

So it told me for example that my value of "80" for 01-27-2022 was present, while in reality it was not, and it just took the value of "80" from 30-03-2022 instead.

Do you know of any way to fix this issue?

 

[Sufiyan97]

Try

Book1
	A	B	C	D	E	F	G	H
1	3/1/2022	-100				3/1/2022	-100	OK
2	3/1/2022	2845				3/1/2022	28.45	NOT OK
3	3/1/2022	71.55				3/1/2022	71.55	OK
4
5
Sheet1

Cell Formulas
Range	Formula
H1:H3	H1	=IF(INDEX($B$1:$B$50,AGGREGATE(15,6,(ROW($A$1:$A$50)-ROW($A$1)+1)/($A$1:$A$50=F1),COUNTIF($F$1:F1,F1)))=G1,"OK","NOT OK")

 

[Jyggalag]

Those formulas are very impressive!!

Thank you so much!

Apologies for not mentioning this earlier, but the real table is a bit messy with dates, so it may look like the example below:

It seems that both formulas (unless I have made an error) are unable to take into account that the value for G8 is located in B28 as well. Is there a fix to this?

Try

Book1
	A	B	C	D	E	F	G	H
1	3/1/2022	-100				3/1/2022	-100	OK
2	3/1/2022	2845				3/1/2022	28.45	NOT OK
3	3/1/2022	71.55				3/1/2022	71.55	OK
4
5
Sheet1

Cell Formulas
Range	Formula
H1:H3	H1	=IF(INDEX($B$1:$B$50,AGGREGATE(15,6,(ROW($A$1:$A$50)-ROW($A$1)+1)/($A$1:$A$50=F1),COUNTIF($F$1:F1,F1)))=G1,"OK","NOT OK")

 

[Jyggalag]

That's how VLOOKUP (and INDEX/MATCH) works. I suspect you want something like COUNTIFS instead.

Do you think it would be possible to do a IF(COUNTIFS) formula with multiple criteria? So it can look at the date as one criteria, and make sure it takes the same date from the original table and the new one, and then see if both tables have a similar date, in which their values match and then return "OK" if yes and "NOT OK" if no?

I tried to do this myself, but I cant get my COUNTIFS formula to work

 

[RoryA]

Do you think it would be possible to do a IF(COUNTIFS) formula with multiple criteria?

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")

 

[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")

This worked!

Amazing Rory, thank you so very much! And apologies, I will make sure to type in the formulas going forward!