Using VLOOKUP to search multiple criteria IN ANY ORDER

**denialcm** · 05-20-2015, 09:38 AM

Hi! I'm trying to use VLOOKUP to search for multiple criteria in a cell in any order. Here's an example of what I'm trying to do:

C-E-R	apples
E-C-R	oranges
C-E	guavas
E-A-C	pineapples
C-R-A-E	strawberries
S-A-E	toothpicks

I want to be able to search "C" and "E", OR to search "E" and "C", and to produce the names of all the fruits no matter the order I put the letters in. I have a VLOOKUP CHOOSE formula that'll, if I look up "C" and "E", I'll get "apples", "guavas", and "strawberries", but not "pineapples" or "oranges". It's something like VLOOKUP(A2&A3,CHOOSE({1,2},B2:B10),1,FALSE) How can I modify what I have so that it produces the names of all the fruits?

**Bernie Deitrick** · 05-20-2015, 12:38 PM

With your table in B2:C7, and C in A2 and E in A3, array enter the formula (enter using Ctrl-Shift-Enter and not just Enter)

=IF(COUNTIF($B:$B,"*"&$A$2&"*"&$A$3&"*")>=ROWS($A$1:A1),INDEX($C:$C,LARGE(NOT(ISERROR(SEARCH("*"&$A$2&"*"&$A$3&"*",$B$2:$B$7)))*ROW($B$2:$B$7),COUNTIF($B:$B,"*"&$A$2&"*"&$A$3&"*")-(ROWS($A$1:A1)-1))),"")

and copy down for 6 rows.