VLOOKUP to return a list of values

**Simon.xlsx** · 03-28-2014, 10:15 AM

Hi All,

I have been searching the forums for an answer but not sure if i am wording it right or even if VLOOKUP will do the job.

I have a list of values in Column B, lets say
Apple
Orange
Pear
Banana

In column A it will state beside each one a TRUE or FALSE statement. Lets say Apple, Pear and Banana have TRUE in column A beside them.

I want a formula like vlookup to look for the TRUE value in column A (in this case 3/4 are true) and return the corresponding values in Column B to a data validation list. or even a list on its own would be helpful.

Currently using the vlookup is only returning the first value beside the first TRUE statement.

Thanks in advance,

Simon

**Ron Coderre** · 03-28-2014, 10:29 AM

This ARRAY FORMULA, completed by holding down CTRL and SHIFT when you press ENTER (instead of just pressing ENTER)
begins the list of Col_A values where the corresponding Col_B value is TRUE

Please Login or Register  to view this content.

Copy D2 and paste into D3 and down as far as you need.

EDITED TO INCLUDE THIS NON-ARRAY FORMULA SOLUTION:

Please Login or Register  to view this content.

Copy that formula down as far as you need.

Is that something you can work with?

**Simon.xlsx** · 03-28-2014, 10:56 AM

Hi Ron,

Thanks for the quick response

I cant seem to get any result from your formula but i can see what you were going for.

I'll keep messing around with what you supplied to try get it to fit,

any other suggestions are of course welcome

Regards,
Simon