VLOOKUP avoiding rows with specific criteria

i have attached two worksheets. Worksheet 1 has the formulas and worksheet 2 is the lookup array.
the formulas are in cells C9,D9 and E9. Normally you would use a IF() function to the check for values
in each row of column B before the formula would activate. Here I'm using a single cell C9, D9 and E9.
The formula automatically moves down the D column and if there is a value in the D column it will go
to WS 2 and find the value. I need the formula to also look at the B column and if it finds a value "BYE'
leave cells C9, D9 and E9 blank. Cell C9 shows where I have tried to add the IF() function and cells
D9 and E9 shoes the formula without the IF() function

*NOTE* THIS PART OF THE FORMULA WORKS =VLOOKUP(LOOKUP(2,1/($D$2:$D$6<>""),$C2:$C6),Sheet2!$B$3:$F$7,4,FALSE)
AND IF YOU WORK WITH THE EXAMPLE BY PLACING VALUES IN COLUMN "D" YOU WILL SEE HOW THE FORMULA MOVES DOWN THE
COLUMN AND PICKS UP THE NEXT ROW.

THE SPECIFIC CRITERIA I NEED IS TO ADD SOMETHING IN THE FORMULA SUCH AN IF() FUNCTION THAT WILL
BYPASS THE ROW IF "BYE" IS IN COLUMN "B". *NOTE*

THE REASON FOR THAT IS THE SPREADSHEET HAS SEVERAL OTHER COLUMNS THAT WILL ACTIVATE AND FILL IN WHICH
I DON'T WANT.

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

Thank you - your title is now fine. I have moved this thread to a more appropriate sub-forum. You now need to be patient and wait for help to be offered.

Whilst you are waiting ...

Administrative Note:

Welcome to the forum.

Although we value your privacy as much you do, it could be important that members have a rough idea of your location as the solutions they offer may be affected by your locale. For instance, you might in the future post questions which are related to your regional settings.

With this in mind, please update your profile to something more precise (something such as UK, Europe, USA, UAE, etc. will suffice).

Thank you for helping us to help you.

I really don't follow the logic of this, but, just maybe....


=IFERROR(VLOOKUP(LOOKUP(2,1/(($D$2:$D$6<>"")*($B$2:$B$6<>"Bye")),$C$2:$C$6),Sheet2!$B:$F,COLUMNS($A:C),FALSE),"")

copied across

I need the formula to also look at the B column and if it finds a value "BYE'
leave cells C9, D9 and E9 blank

Please Login or Register  to view this content.

1. Look at the formula:

=IFERROR(VLOOKUP(LOOKUP(2,1/(($D$2:$D$6<>"")*($B$2:$B$6<>"Bye")),$C$2:$C$6),Sheet2!$B:$F,COLUMNS($A:C),FALSE),"")

It is looking for the non-presence of bye in column B.



2. If ProtonLeah hasn't got it right, repost your sheet, showing some manually calculated answers for the senarios you need to cover.

Feel free. If its too big (>1 Mb) zip it or save as .xlsb. It's Sat nite here and I'm away off to be irresponsible (socially distanced) for a while. Back tomorrow.

All sorted then??

You're welcome.

---

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

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.