return next value if mutlple values match

[raj soni]

hello friends ,
i am trying to configure formula ,
thats searches E1 AND F1 , through A and B column , and returns its corresponding value in c column in cell E2 AND E3 AND SO ON....
but problem rises when column a and b has two or more combination of E1 and F1 value .
as a example i have attach sample file ...

[JohnTopley]

Example is EMPTY! Please post a sample Excel file not image.

[raj soni]

yes sir , attach sample file ....

[Tony Valko]

This array formula** entered in E3:

=IFERROR(INDEX(C2:C15,MATCH(1,(A2:A15=E1)*(B2:B15=F1)*(C2:C15=E2),0)+1),"")

** 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.

[raj soni]

hello sir ,
its not showing any values ,
i want to search E1 and F1 combination in column a and b .
as you can see in attach sample file ,
there are two such combination .
i want to return corresponding value of c column in e2 cell .
since it has such sample combination , i want to return second corresponding value in e3 .

[Tony Valko]

Here's your file with the formula entered.

As you can see it returns the results that you have indicated.

The formula must be array entered.

Array formulas are entered differently than a regular formula. After you type in a regular formula you hit the ENTER key. With an array formula you must use a combination of keys. Those keys are the CTRL key, the SHIFT key and the ENTER key. That is, hold down both the CTRL key and the SHIFT key then hit the ENTER key.

When done properly Excel will enclose the formula in squiggly brackets { }. You can't just type these brackets in, you must use the key combo to produce them. Also, anytime you edit an array formula it must be re-entered as an array using the key combo.

[raj soni]

sir , you have still not got my question properly ,

i am trying to do below link formula with 2 lookups ..

http://thinketg.com/how-to-return-mu...ch-or-vlookup/

also i have attach sample file again ....

[Tony Valko]

You changed the expected result in this latest file.

Data Range

	A	B	C	D	E	F	G
1	Animal	Name	color		Lookup:	Mouse	Mickey
2	Duck	Daffy	red		will return	green
3	Mouse	Mickey	green		will return	white
4	Mouse	Mickey	white
5	Bunny	Bugs	black
6	Pig	Porky	red
7	Dog	Goofy	green
8	Dog	Pluto	white
9	Dog	Pluto	black
10	------	------	------	------	------	------	------

This array formula** entered in F2:

=IFERROR(INDEX(C:C,SMALL(IF((A$2:A$9=F$1)*(B$2:B$9=G$1),ROW(C$2:C$9)),ROWS(F$2:F2))),"")

** 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.

[bebo021999]

May be you need to establish a list.

in F2:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Drag down.

Copy column F to colum J

[raj soni]

thanks , Tony Valko and bebo021999
, its working perfectly now .......

[Tony Valko]

You're welcome. We appreciate the feedback!

If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.