[VLOOKUP] Next value if cell contains specific text

[Denox78]

Hello everyone,

I'm a beginner on Excel so I don't know if my question is an easy one or not.

Basically i'm using a simple vlookup "=VLOOKUP(F2,B:C,2,FALSE)"

I would like to know if it's possible to skip the first match C2 if cell D2 contains specific text like "Done" and return value C3 ?

See my example below :

3423.jpg


Thanks for your help

[AliGW]

Welcome to the forum.

Try this:

=INDEX(C:C,MATCH(1,(B:B=F2)*(C:C="In Progress"),0))

[Denox78]

I corrected the C:C at the end as D:D and it's working now !

=INDEX(C:C,MATCH(1,(B:B=F2)*(D:D="In Progress"),0))

is it possible to add more words than "In Progress" ? If yes what syntax should I use

Thanks for your help

[AliGW]

Yes, and well spotted!

Hopefully this sort of thing will work for you:

=INDEX(C:C,MATCH(1,(B:B=F2)*((D:D="In Progress")+(D:D="Something Else")+(D:D="Another Option")),0))

Or this way, perhaps:

=INDEX(C:C,MATCH(1,(B:B=F2)*(D:D<>"In Progress"),0))

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

[6StringJazzer]

An image attachment has limited value. In the future just attach the Excel file. It's easier than taking a screenshot first and then attaching that. Having your file allows us to see and experiment with your data, layout, formulas, code, and possibly attach a file with a completed solution. See yellow banner at the top of the page.

It's more complicated than just VLOOKUP.

Here is one solution:

=INDEX(C:C,MATCH(F2,OFFSET($B$1,MATCH(F2&"Done",B:B&D:D,0),0,99))+MATCH(F2&"Done",B:B&D:D,0))

This is an array formula. After typing in the formula, do not hit ENTER--hit CTRL+SHIFT+ENTER. You have done it correctly if the formula in the formula box has {braces} around it. You cannot type in the braces; they are just an indicator that it is an array formula.

Implemented in attached example. By the way, I don't know what you're doing but it is a bad practice to store unencrypted passwords.

[6StringJazzer]

I took it quite literally when the spec said skip if like "Done". If your requirement is a little different than that then Ali's solution is probably more elegant and easier for you to use.

[Denox78]

Ok well noted, thank you both for your help i'm closing the case