Help Needed in Using Index Array w/2 Search Criteria in Excel

[cliff.sizemore]

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I am attempting to use Excel to search through several hundred thousand records and return a unique identifier based on two user defined inputs. <o></o>
<o></o>
I have succesfully used an Index Array formula in order to search these records to return the unique identifier based on ONE user input but my syntax breaks down when I attempt to incorporate a SECOND criteria into the Index Array. <o></o>

In the example above, I'm trying to write the formula in cell K8 that would allow the two user inputs in cells L3 and L4 to be used to search the range A2:F17 and return the corresponding Comp ID's (this is the unique idenitfier that I need) from Column B.<o></o>
<o></o>
Any suggestions on structuring a formula to help me get the desired results would be GREATLY APPRECIATED. Thanks in advance.<o></o>
<o></o>
FYI<o></o>
<o></o>
I'm using Excel 2010 running on Windows XP<o></o>

 

[adam087]

Try
=INDEX($B$2:$B$17, MATCH(L2&L3, $A$2:A$A$17&$F$2:$F$17, 0))

Confirmed with CTRL+SHIFT+ENTER rather than just with Enter.

Regards
Adam

 

[cliff.sizemore]

Try
=INDEX($B$2:$B$17, MATCH(L2&L3, $A$2:A$A$17&$F$2:$F$17, 0))

Confirmed with CTRL+SHIFT+ENTER rather than just with Enter.

Regards
Adam

Hi Adam,

Thanks for the quick response to my post. I applied the formula suggested but it unfortunately only returned the first Comp ID from Column B that met the User Entered criteria (the 899655 from cell B9).

How do we modify the formula to include ALL comp ID's in column B that meet both search criteria? In my example screenshot, the formula should also return the comp ID's in cells B12 & B13, for a grand total of 3 matches to the search criteria.

Thanks again.

 

[adam087]

Hmmm. My formula is only good if there's only one option per combination. Sorry I didn't spot that. I can't help but I suffer little doubt someone smarter than me will stop by shortly.

Regards
Adam

 

[cliff.sizemore]

Not a problem- its the second search criteria my boss asked me to include that blew up my very amateur efforts as well. I was feeling good after solving the single criteria search on my own and then he knocked me down a few notches by putting in the second.

I do appreciate the effort to help however, thanks!

 

[Aladin Akyurek]

K8, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($B$2:$B$17,
  SMALL(IF($A$2:A$A$17&"|"&$F$2:$F$17=$L2&"|"&$L3,
   ROW($B$2:$B$17)-ROW($B$2)+1),ROWS($K$8:K8))),"")

 

[sysp34]

i dunno if this will helps since we need to create another column for index criteria, master excelsior Aladin Akyurek already posted along time ago

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	store	CompID	CompName	CompBrand	Address	Zip		searchcriteria
2	104	1	a	j	aj	10		store	104
3	104	2	b	i	bi	10		zip	10
4	104	3	c	h	ch	11
5	112	4	d	g	dg	10		searchresults(compid'sbystore&zip)
6	112	5	e	f	ef	10		store	zip
7	112	6	f	e	fe	11		1	10	110
8	123	7	g	d	gd	10		2	10	210
9	123	8	h	c	hc	10		3	10	310
10	200	9	i	b	ib	11			10	10
11	200	10	j	a	ja	10			10	10
12
Sheet1

 

[sysp34]

using master excelsior Aladin Akyurek, awesome formula d(^_^)b

Index Array w/2 Search Criteria.xlsx
	A	B	C	D	E	F	G	H	I
1	store	CompID	CompName	CompBrand	Address	Zip		searchcriteria
2	104	1	a	j	aj	10		store	104
3	104	2	b	i	bi	10		zip	10
4	104	3	c	h	ch	11
5	112	4	d	g	dg	10		searchresults(compid'sbystore&zip)
6	112	5	e	f	ef	10		1
7	112	6	f	e	fe	11		2
8	123	7	g	d	gd	10
9	123	8	h	c	hc	10
10	200	9	i	b	ib	11
11	200	10	j	a	ja	10
Sheet2

 

[cliff.sizemore]

Thanks to everyone who replied- I've used your suggestions and got the search function working.

Your help is TRULY appreciated!!!!

 

[Aladin Akyurek]

Thanks to everyone who replied- I've used your suggestions and got the search function working.

Your help is TRULY appreciated!!!!

You are welcome.