Using VLOOKUP to search multiple criteria in ANY ORDER

**denialcm** · 05-18-2015, 04:41 PM

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?

**Tony Valko** · 05-18-2015, 06:20 PM

Try this...

Data Range

	A	B	C	D	E	F
1	C-E-R	apples		C	E	apples
2	E-C-R	oranges				oranges
3	C-E	guavas				guavas
4	E-A-C	pineapples				pineapples
5	C-R-A-E	strawberries				strawberries
6	S-A-E	toothpicks
7	------	------	------	------	------	------

This array formula** entered in F1:

=IFERROR(INDEX(B:B,SMALL(IF(ISNUMBER(SEARCH($D$1,$A$1:A$6)+SEARCH(E$1,A$1:A$6)),ROW(A$1:A$6)),ROWS(F$1:F1))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.