Nested If with Vlookup, based on a Yes/no selection

Hi, I have a workbook that contains several sheets. One is the "Master list" ("Membership List") from which I need to extract data to put in others. I've managed to work some vlookup functions that extract data for some sheets which work OK, but I'm struggling to write a formula to extract vlookup data based on the contents of a particular Yes / No column in the "Membership List".

I've done a simple workbook attached (the original has far more columns, but they aren't relevant to this post) - what I need to do is to populate the contents of the "Dining List" with the names of those that are actually dining, ie, where the "Dining?" column has a "Yes".

I'm OK with the vlookup bit, but can't seem to get the correct syntax for the "If G (2 - 11) "Yes", then populate the cells in the Dining list; If "No", ignore the row.

I would also like to bring in the "IFERROR" statement as well but that's far too much for my abilities.

Not sure what the result will look like in terms of rows in the Dining List, but I need the rows to be without blank rows, ie in this instance there will only be 6 rows in the sheet.

I hope someone can help - it's probably a simple formula to an expert....
Thanks in advance.
Rgds
Mike Waring

Sorry, just noticed the stated versions of windows and Excel are out of date - I'm using W10, with Office 2021 Pro Plus

edit - just re-read your edit

so filter should work
but you want non contiguous columns
just need to look that up in my examples

=FILTER(FILTER('Membership List'!C2:H11,'Membership List'!G2:G11="yes"),{1,1,1,0,0,1})
can also add SORT

you should also update the profile to show the version of excel you are using

not sure what the IFERROR is for

formula(s) - I only posted 1

=FILTER(FILTER('Membership List'!C2:H11,'Membership List'!G2:G11="yes"),{1,1,1,0,0,1})

and that goes into C2

BUT nothing else in the other cells - but then i would expect a spill error

Not sure why you are getting a value - unless its a different spreadsheet to example

you said you are using office 2021 - which should have the FILTER function
but i would expect a name error


added an image

spill error - usually means the other cells are not clear
so C2 to F20 ???? or more must be clear
select that range or more and clear contents - so if you expect a result to go down as far as say row100 - that needs to be clear

now JUST paste the formula into Cell C2 only

its an array so all the other cells will show the values automatically
you dont copy or paste to the other cells

can you post the formula you are using
perhaps the actual sheet - if no sensitive info

Note
i use 2 filter functions
one with an array
{1,1,1,0,0,1}
which shows which columns to show
that must match up with the range you are using

you profile has not updated

calc is an error in the formula results , criteria not matching - or range not matching

https://support.microsoft.com/en-us/...your%20formula.

#CALC! errors occur when Excel's calculation engine encounters a scenario it does not currently support. Here's how to address specific #CALC! errors:

spill is not space to put the array , as cells not blank

https://support.microsoft.com/en-us/...2-ef9cc9ad4023

#SPILL errors are returned when a formula returns multiple results, and Excel cannot return the results to the grid. For more details on these error types, see the following help topics:

if you could just change the names/email and post -
rules do not allow off forum assistance

d to i
10 columns
if you are retuning all 10 to the list
A to Q is a lot more

so if you choose the range
=FILTER(FILTER('Membership List'!A2:Q11,'Membership List'!G2:G11="yes"),
now this needs to show the columns to return based on A
a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q
this is only needed if you are not returning all the columns - lets say you just want A,B,C,F
are 1 is placed to represent the column to return
A,B,C,0,0,F,0,0,0,0,0,0,0,0,0,0,0
{1,1,1,0,0,1,0,0,0,0,0,0,0,0,0,0,0})

=FILTER(FILTER('Membership List'!A2:Q11,'Membership List'!G2:G11="yes"),{1,1,1,0,0,1,0,0,0,0,0,0,0,0,0,0,0})


can you just post the formula and what columns are in the range and you want to show and what column the F is in



the matrix of 1 and 0 - starts at whatever column your filter range starts

as i write this - not sure i'm explaining very well

=FILTER(FILTER('Membership List'!C2:H11,'Membership List'!G2:G11="yes"),{1,1,1,0,0,1})

Column C in the membership list is the first column you want to show
and column H is the last column
so
=FILTER(FILTER('Membership List'!C2:H11
now we need the criteria anc column G has the yes - so we add that column as a criteria
=FILTER(FILTER('Membership List'!C2:H11,'Membership List'!G2:G11="yes")

Now if that was ALL the columns to show then
FILTER('Membership List'!C2:H11,'Membership List'!G2:G11="yes")
would do that
But you only want to show columns

C, D, E & H

so we add the extra FILTER and use the
{1,1,1,0,0,1}
matrix
C,D,E,F,G,H
but F & G has zero - so will not show in the array

hence the result shows
C,D,E,H columns in the result

=FILTER(FILTER('Membership List'!C2:H11,'Membership List'!G2:G11="yes"),{1,1,1,0,0,1})

no

here is a mock up of what i think you are saying

no problem , you are welcome, and glad we got to the result

I found this in another forum a while back

=FILTER(FILTER(IF(ISNUMBER('Brethren Master List Feb 2023'!D2:I64),'Brethren Master List Feb 2023'!D2:I64,'Brethren Master List Feb 2023'!D2:I64&""),'Brethren Master List Feb 2023'!$I$2:$I$64="yes"),{1,1,1,0,0,1})

Also with 365 version , 2021 version there is LET() -
=LET(f,FILTER(FILTER('Brethren Master List Feb 2023'!D2:I64,'Brethren Master List Feb 2023'!I2:I64="yes"),{1,1,1,0,0,1}),IF(f=0,"",f))

Which i'm still working on

f is a variable and is set to
so this part
LET(f,FILTER(FILTER('Brethren Master List Feb 2023'!D2:I64,'Brethren Master List Feb 2023'!I2:I64="yes"),{1,1,1,0,0,1})
and then
IF(f=0,"",f)
which changes a zero to a blank

you are welcome

i thought i would see what happens if there is actually a 0 in a cell
so that would need to be brought across and NOT a blank

the Let() still made it blank
wheres as
=FILTER(FILTER(IF(ISNUMBER('Brethren Master List Feb 2023'!D2:I64),'Brethren Master List Feb 2023'!D2:I64,'Brethren Master List Feb 2023'!D2:I64&""),'Brethren Master List Feb 2023'!$I$2:$I$64="yes"),{1,1,1,0,0,1})
correctly identified "" and 0