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.