Index Match Multiple results

**kashifshahzad** · 03-21-2017, 08:50 AM

I have two sheets in a file
First Sheet is Op Having Fields
S/No.Emp. No. Emp. Name Equip. Code
31 20320 AHMED MAHDI ESMAIEL GAD MC43
32 33468 SAEEDULLAH KHAN DAWA KHAN MC43

Second Sheet Eq I have following Fields
EQUIP. CODE Operator No Name
MC43 20320 AHMED MAHDI ESMAIEL GAD
MC43 20320 AHMED MAHDI ESMAIEL GAD

In Eq Sheet I have used following formula

=IFERROR(INDEX(Op!$A$2:$D$1001,MATCH(A12,Op!$D$2:$D$1001,0),3)," ")

Its giving the result but only the first found number.
In my scenario I have Equip. Code Operator in Op Sheet Which may have more than one Operator No.
In Eq sheet I need both of the Operator no having the same Equip Code.

**Glenn Kennedy** · 03-21-2017, 09:48 AM

Here's one way, using array formulae, copied to the right, like this oe:

=IFERROR(INDEX(Op!$B:$B,SMALL(IF(Op!$D$2:$D$36=$A2,ROW(Op!$D$2:$D$36)),COLUMNS($B:B))),"")Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

**Glenn Kennedy** · 03-21-2017, 09:49 AM

Incidentally, I have just noticed that your profle says Excel 2003, while you posted an .xlsx file, which is Excel 2007 and later. Which do you use? My formula will need to be modified if you are really are using Excel 2003.

**kashifshahzad** · 03-21-2017, 10:26 AM

First of all thank you very much for your reply.
I am using excel 2013. its really worked for me. you added multiples columns and data is coming like operator 1,2,3,4 and name 1,2, 3,4
here I would like to ask if its possible in excel that without adding extra columns it check first EQUIP. CODE in Op Sheet bring the first one, on next row it brings second value. as per knowledge its not applicable in excel but still I am confirming if there is a way.

**Glenn Kennedy** · 03-21-2017, 10:37 AM

Yes... Like this. It's just not as convenient for copying/pasting.

Can you amend your profile (User CP/My settings/Edit profile to correctly identify which Excel version you are using. By comparison with E-2013, E-2003 is a bit pre-historic and limited...

**kashifshahzad** · 03-21-2017, 10:42 AM

Thank you very much...

**kashifshahzad** · 03-21-2017, 10:45 AM

I m trying to change excel version in profile but I click on save change button its gives me error message "A required field called Location is missing or has an invalid value.."

**Glenn Kennedy** · 03-21-2017, 11:14 AM

It works OK for me. I just changed mine to Excel 2010... mostly. Maybe you could send a PM to one of the Forum Mods... AliGW, 6StringJazzer and JBeaucaire are here v. frequently. One of them (6SJ) is online now. Ask one of them nicely....

Index Match Multiple results

LinkBack

Thread Tools

Rate This Thread

Display

Index Match Multiple results

Re: Index Match Multiple results

Re: Index Match Multiple results

Re: Index Match Multiple results

Re: Index Match Multiple results

Re: Index Match Multiple results

Re: Index Match Multiple results

Re: Index Match Multiple results

Thread Information

Users Browsing this Thread

Similar Threads

Multiple results Index Match

[SOLVED] Sum of multiple index/match results

Index Match with multiple results

[SOLVED] Index, Match, Multiple Results

Index/Match with multiple results

Need help with Index + Match Multiple Results

Index and Match and multiple results

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions