Excel - INDEX Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I'm having trouble with an index & match formula where I'm looking up two different pieces of info in order to return a specific value.
Solved by K. D. in 54 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 09/10/2018 - 12:33
Welcome to ExcelChat!
User 09/10/2018 - 12:33
Thanks
Excelchat Expert 09/10/2018 - 12:33
Do you have any sample data?
Excelchat Expert 09/10/2018 - 12:34
Are you looking for a value with more than one criteria?
User 09/10/2018 - 12:34
I have two spreadsheets open on my desktop. Is there any way we can share my screen? I can not upload these due to the sensitive nature of the data
Excelchat Expert 09/10/2018 - 12:34
Ok
Excelchat Expert 09/10/2018 - 12:35
Can you modify just a part of the file?
Excelchat Expert 09/10/2018 - 12:35
and put the data here?
User 09/10/2018 - 12:35
I can try. Can I copy & paste?
Excelchat Expert 09/10/2018 - 12:35
Yes you can
Excelchat Expert 09/10/2018 - 12:36
Use sheet1 and sheet2 to simulate the two spreadsheets
User 09/10/2018 - 12:39
Ok. I want F2 to display the value on the data sheet by looking up the CSS# and PIN.
Excelchat Expert 09/10/2018 - 12:39
OK
Excelchat Expert 09/10/2018 - 12:39
Before we get started, this is a reminder that our policy is 1 problem per session with additional Q&A on that problem as time allows.
User 09/10/2018 - 12:39
There could be many combinations of PIN and CSS but there would never be a duplicate
Excelchat Expert 09/10/2018 - 12:39
CSS# is column A in data?
User 09/10/2018 - 12:40
yes
Excelchat Expert 09/10/2018 - 12:40
Pin column B in data
Excelchat Expert 09/10/2018 - 12:40
What column do you need to returns from data?
User 09/10/2018 - 12:41
let's just say its on another sheet
Excelchat Expert 09/10/2018 - 12:41
OK
User 09/10/2018 - 12:41
Column E on Data tab
Excelchat Expert 09/10/2018 - 12:41
OK, Got it
Excelchat Expert 09/10/2018 - 12:41
In the table on sheet 1
Excelchat Expert 09/10/2018 - 12:42
We have the CSS#, but the pin is in another sheet
User 09/10/2018 - 12:43
This is more what it looks like on mine. PIN corresponds to a sales assocaite
Excelchat Expert 09/10/2018 - 12:43
Ok
User 09/10/2018 - 12:43
One sheet per associate with a list of customers.
User 09/10/2018 - 12:43
DATA tab represents a seprate file that has customer spend info in the format I've used here
Excelchat Expert 09/10/2018 - 12:43
Got it, Let me create the formula with INDEX and MATCH
User 09/10/2018 - 12:44
That's what I'm currently using. Its works in some cells but not others
Excelchat Expert 09/10/2018 - 12:45
Ok
Excelchat Expert 09/10/2018 - 12:45
What configuration do you have on your sheet?
Excelchat Expert 09/10/2018 - 12:46
For the current sheet1
Excelchat Expert 09/10/2018 - 12:46
=ArrayFormula(INDEX(Data!E:E,MATCH(1,(B4=Data!A:A)*($A$2=Data!B:B),0)))
Excelchat Expert 09/10/2018 - 12:46
In excel you need to enter an array formula pressing CTRL+SHIFT+ENTER
User 09/10/2018 - 12:46
yes.
User 09/10/2018 - 12:47
In my formula =ArrayFormula(INDEX(Data!E:E,MATCH(1,(B4=Data!A:A)*($A$2=Data!B:B),0))) E;E looks like E$:E$
User 09/10/2018 - 12:48
would that make a difference
Excelchat Expert 09/10/2018 - 12:48
Do you have E:E at the end?
User 09/10/2018 - 12:48
I'm telling it to look in the entire column, not just specific cells in that column
User 09/10/2018 - 12:48
Np
User 09/10/2018 - 12:48
No
Excelchat Expert 09/10/2018 - 12:48
OK
User 09/10/2018 - 12:48
It's same place as yours but has $
Excelchat Expert 09/10/2018 - 12:49
Yes
User 09/10/2018 - 12:49
should I remove $?
User 09/10/2018 - 12:49
I know that $ locks the formula on certain cells
Excelchat Expert 09/10/2018 - 12:49
Should be the same
User 09/10/2018 - 12:50
Ok. That's why I was hoping to share my screen so you can see where I went wrong
Excelchat Expert 09/10/2018 - 12:50
You can send a ScreenShoot
Excelchat Expert 09/10/2018 - 12:51
Send me a screenshot of the table with the formula
User 09/10/2018 - 12:51
Here it is
[Uploaded an Excel file]
Excelchat Expert 09/10/2018 - 12:52
OK
Excelchat Expert 09/10/2018 - 12:52
Just the order is wrong
Excelchat Expert 09/10/2018 - 12:52
in the match
Excelchat Expert 09/10/2018 - 12:53
First you need the value and after that =range
Excelchat Expert 09/10/2018 - 12:53
for example:
Excelchat Expert 09/10/2018 - 12:53
{=INDEX(range1,MATCH(1,(A1=range2)*(B1=range3)*(C1=range4),0))}
Excelchat Expert 09/10/2018 - 12:54
this is for 3 criterias, if take a look, A1 is the value= to the range
User 09/10/2018 - 12:55
not following
Excelchat Expert 09/10/2018 - 12:55
MATCH(B43='[FALL....)
Excelchat Expert 09/10/2018 - 12:55
OK, please copy and paste the formula here
Excelchat Expert 09/10/2018 - 12:55
I will modify the formula and explain
User 09/10/2018 - 12:55
=IFERROR(INDEX('[Fall FY19 Customer Spend.xlsx]September'!$E:$E,MATCH(1,('[Fall FY19 Customer Spend.xlsx]September'!$A:$A=B43)*('[Fall FY19 Customer Spend.xlsx]September'!$B:$B=$J$2),0)),0)
Excelchat Expert 09/10/2018 - 12:56
=IFERROR(INDEX('[Fall FY19 Customer Spend.xlsx]September'!$E:$E,MATCH(1,(B43='[Fall FY19 Customer Spend.xlsx]September'!$A:$A)*($J$2='[Fall FY19 Customer Spend.xlsx]September'!$B:$B),0)),0)
Excelchat Expert 09/10/2018 - 12:57
This should work. Because the array compare a value with an array, not an array with a value
Excelchat Expert 09/10/2018 - 12:58
Try the formula and let me know.
User 09/10/2018 - 12:58
Hmm. Nope.
User 09/10/2018 - 12:58
I hit Shift CTR ENR
Excelchat Expert 09/10/2018 - 12:58
Did you try the formula I just sent you?
User 09/10/2018 - 12:58
Yes
Excelchat Expert 09/10/2018 - 12:59
Didn't work?
User 09/10/2018 - 12:59
It did not
Excelchat Expert 09/10/2018 - 12:59
Returns 0?
Excelchat Expert 09/10/2018 - 12:59
Or an error?
User 09/10/2018 - 12:59
And I know there is data present in the other sheet that matches the CSS# and PIN on the same line
User 09/10/2018 - 01:00
Yes, 0
Excelchat Expert 09/10/2018 - 01:00
Got it
Excelchat Expert 09/10/2018 - 01:00
Is this the example you posted here?
User 09/10/2018 - 01:01
Yes. Copied directly from the sheet
Excelchat Expert 09/10/2018 - 01:01
Ummm let me double check the formula again, because here is working
User 09/10/2018 - 01:01
Is there a limit to how many functions it can calculate or lines it can look at?
Excelchat Expert 09/10/2018 - 01:02
The limit is the limit of the spreadsheet
User 09/10/2018 - 01:02
Meaning that it stops where the data does?
Excelchat Expert 09/10/2018 - 01:03
When it finds the first match it stops
Excelchat Expert 09/10/2018 - 01:04
Delete the part of IFERROR
Excelchat Expert 09/10/2018 - 01:05
Let me know the error
User 09/10/2018 - 01:05
#N/A
Excelchat Expert 09/10/2018 - 01:05
OK
Excelchat Expert 09/10/2018 - 01:06
One moment more please.
Excelchat Expert 09/10/2018 - 01:06
Pin is J2 right?
User 09/10/2018 - 01:07
correct
Excelchat Expert 09/10/2018 - 01:07
And it is working with the other values
Excelchat Expert 09/10/2018 - 01:07
CSS# is in text format or number format?
User 09/10/2018 - 01:08
number
User 09/10/2018 - 01:08
as is the corresponding value in the other sheet
Excelchat Expert 09/10/2018 - 01:08
OK
User 09/10/2018 - 01:08
Not sure if this helps
[Uploaded an Excel file]
Excelchat Expert 09/10/2018 - 01:09
OK, let me see
Excelchat Expert 09/10/2018 - 01:10
Lets see what part of the formula is returning the error
User 09/10/2018 - 01:10
how do I do that
Excelchat Expert 09/10/2018 - 01:10
I will send you two formulas
Excelchat Expert 09/10/2018 - 01:11
=match(PIN NUMBER,Data!B:B,0)
Excelchat Expert 09/10/2018 - 01:11
Replace PIN NUMBER with the pin
User 09/10/2018 - 01:11
Where?
Excelchat Expert 09/10/2018 - 01:12
I some empty cell
Excelchat Expert 09/10/2018 - 01:12
In*
Excelchat Expert 09/10/2018 - 01:12
Let me modify the formula to your workbook
Excelchat Expert 09/10/2018 - 01:13
one moment
Excelchat Expert 09/10/2018 - 01:13
=MATCH(B43,'[Fall FY19 Customer Spend.xlsx]September'!$A:$A,0)
Excelchat Expert 09/10/2018 - 01:13
Paste it in an empty cell
User 09/10/2018 - 01:14
use SHFT CTR ENTER?
Excelchat Expert 09/10/2018 - 01:14
No
User 09/10/2018 - 01:14
it says 188
Excelchat Expert 09/10/2018 - 01:14
Ok
Excelchat Expert 09/10/2018 - 01:14
Lets try with the other one
Excelchat Expert 09/10/2018 - 01:14
One moment
Excelchat Expert 09/10/2018 - 01:15
=MATCH($J$2='[Fall FY19 Customer Spend.xlsx]September'!$B:$B)
Excelchat Expert 09/10/2018 - 01:15
Try that one in another empty cell.
User 09/10/2018 - 01:16
It's thinking
Excelchat Expert 09/10/2018 - 01:16
Without Ctrl+Shift+enter
Excelchat Expert 09/10/2018 - 01:16
Just enter
User 09/10/2018 - 01:17
[Uploaded an Excel file]
User 09/10/2018 - 01:17
error message
Excelchat Expert 09/10/2018 - 01:17
OK, one moment
Excelchat Expert 09/10/2018 - 01:18
Sorry I missed a comma
User 09/10/2018 - 01:18
no problem
User 09/10/2018 - 01:19
Where does comma go?
Excelchat Expert 09/10/2018 - 01:19
=MATCH($J$2,'[Fall FY19 Customer Spend.xlsx]September'!$B:$B,0)
User 09/10/2018 - 01:19
now it returns 429
Excelchat Expert 09/10/2018 - 01:19
So this is the problem
Excelchat Expert 09/10/2018 - 01:20
Should return the same value
Excelchat Expert 09/10/2018 - 01:20
188
Excelchat Expert 09/10/2018 - 01:20
Because the match Should occure in the same line
Excelchat Expert 09/10/2018 - 01:20
Go to row 188 in September file
Excelchat Expert 09/10/2018 - 01:20
Take a look to Pin number
User 09/10/2018 - 01:22
omg. I was using the wrong PIN. I should have noticed
Excelchat Expert 09/10/2018 - 01:22
Got it!!
Excelchat Expert 09/10/2018 - 01:23
This should be the issue.
User 09/10/2018 - 01:23
I feel so dumb. LOL I've been looking at spreadsheets for too long today
Excelchat Expert 09/10/2018 - 01:23
Don't worry that happens!
User 09/10/2018 - 01:23
BUT, you did re-order my formula. Is it strange that they both seem to be working?
Excelchat Expert 09/10/2018 - 01:24
That's Ok
User 09/10/2018 - 01:24
Or do you think mine may not be working in some places and I just haven't noticed?
Excelchat Expert 09/10/2018 - 01:24
Sometimes you have to re-order the formula
Excelchat Expert 09/10/2018 - 01:24
If it works for you that's great
User 09/10/2018 - 01:25
It seems to work. But I'll save yours just incase
Excelchat Expert 09/10/2018 - 01:25
Got it!
Excelchat Expert 09/10/2018 - 01:25
Do you believe that will address your problem?
User 09/10/2018 - 01:26
Thank you for your help. I think so
Excelchat Expert 09/10/2018 - 01:26
Got it!
Excelchat Expert 09/10/2018 - 01:26
Thanks for using ExcelChat!
Excelchat Expert 09/10/2018 - 01:26
Please leave any feedback and rate this session.
User 09/10/2018 - 01:27
I will. Thank You
Excelchat Expert 09/10/2018 - 01:27
We are here at your service!
Excelchat Expert 09/10/2018 - 01:27
Have a greate day!

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc