VLookup only if Conditions meet

mohdamir1989

New Member
Joined
Oct 17, 2017
Messages
40
Hi All,

I am trying to achieve a simple logic. Tried to search over google and forum before asking but couldn't get anyway near.

I am currently using below formula which is working fine but need to manually delete extra rows to obtain conditions.

(In Table) =IFERROR(VLOOKUP([@[Mobile Number]]+120000000000,Worksheet[[#All],[Column2]:[Reply]],5,0),"")
(Just for clarification Without Table) =IFERROR(VLOOKUP(X2+120000000000,Sheet1!B:F,5,0),"")

I want vLookup to work only if value is equal to "Y" or "N" and ignore all other values in that column (From where the results are being pulled).

Is it possible by anyway?

Thanking in anticipation.

Best Regards
Amir
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

VBE313

Well-known Member
Joined
Mar 22, 2019
Messages
644
Office Version
  1. 365
Platform
  1. Windows
Code:
=IF(OR(IFERROR(VLOOKUP(X2+120000000000,Sheet1!B:F,5,0),"")="Y",IFERROR(VLOOKUP(X2+120000000000,Sheet1!B:F,5,0),"")="N"),IFERROR(VLOOKUP(X2+120000000000,Sheet1!B:F,5,0),""),"")
 

mohdamir1989

New Member
Joined
Oct 17, 2017
Messages
40
Code:
=IF(OR(IFERROR(VLOOKUP(X2+120000000000,Sheet1!B:F,5,0),"")="Y",IFERROR(VLOOKUP(X2+120000000000,Sheet1!B:F,5,0),"")="N"),IFERROR(VLOOKUP(X2+120000000000,Sheet1!B:F,5,0),""),"")
Thank you so much @VBE313 for a prompt response. It is working perfectly fine if the data is "Newest to Oldest" date wise. But not working if the data is "Oldest to Newest". As Vlookup considers first value only. Is there any other way out.
I am actually working around the customer's first and last feedback and to do so I have 2 different sheets. I am using different sorting in both of the sheets to find the responses. For example, the customer replied, "N" dated 02-02-2020 and later he converted to "Y" dated 05-02-2020 and on date 02-02-2020 he also replied "1" which here I am ignoring.

By using this formula it returns first feedback "Y" but not considering it the last, as the customer also replied "1".

Looking forward to your valuable feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,108,644
Messages
5,524,050
Members
409,558
Latest member
Excelinho

This Week's Hot Topics

Top