Need help on Index and Match function

[DuncanKeenen]

Hi all,

I am new to Index Match function.

Need help to extract data from array data and display on different sheet in same worksheet.

Pls find below my tables in excel,

LOCATION SALES PERSON ITEM QTY SOLD BALANCE
EAST MIKE RADIO 5 15
EAST MIKE TABLE 3 10
WEST RAY TV 1 49
EAST SAM RADIO 3 12
EAST BOB TABLE 9 1
EAST SAM TV 10 39


LOCATION MIKE RAY SAM BOB
EAST RADIO
WEST #N/A
WEST TV
EAST RADIO
EAST RADIO
EAST TABLE

Problem 1 : Under "Mike", i get "N/A" for West Location. Can it be blank?
Problem 2 : Under "Sam", can it find the next record as it captured Radio instead of TV?

{=INDEX(C2:C7,MATCH(I4&K1,A2:A7&B2:B7,0))}
{=INDEX(C2:C7,MATCH(I7&M1,A2:A7&B2:B7,0))}

Thanks for your help in advance.

Duncan

[FDibbins]

Hi, welcome to the forum

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(note: this change is not optional )

Also, it often helps if you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[DuncanKeenen]

apologize as i am new and looking for solution in this forum.

i have click on the "attachment" icon but just a small dialog box. Now i just managed to find out how to do it.

i will amend the title.

Thanks for the advise

[DuncanKeenen]

Hi,

I have attached the excel for easy reference to my problem.

Will appreciate your help.

Thanks alot in advance

DK

[FDibbins]

If you can change teh order of Location, so all locations are grouped...

	N	O	P	Q	R
1	LOCATION	MIKE	RAY	SAM	BOB
2	EAST	RADIO		RADIO	TABLE
3	EAST	TABLE		TV
4	EAST
5	EAST
6	WEST
7	WEST

then O2=IFERROR(INDEX($C:$C,SMALL(IF(($A$2:$A$7=$N2)*($B$2:$B$7=J$1),ROW($A$2:$A$7)),ROWS($A$1:$A1))),"")
...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.
then copy down and across as needed

[DuncanKeenen]

Hi FDibbins,

@O2, =IFERROR(INDEX($C:$C,SMALL(IF(($A$2:$A$7=$N2)*($B$2:$B$7=J$1),ROW($A$2:$A$7)),ROWS($A$1:$A1))),""), i got Radio

@O3, =IFERROR(INDEX($C:$C,SMALL(IF(($A$2:$A$7=$N3)*($B$2:$B$7=J$1),ROW($A$2:$A$7)),ROWS($A$1:$A1))),""), i got Radio again.

Sorry, what should i enter for the function @O3.

Apologise, dont quite understand the iferror function.

Thanks alot in advance.

Best rgds
Duncan

[DuncanKeenen]

Hi FDibbins,

I got it already.

Thanks alot for your great help.

Much appreciated.

Best rgds
Duncan