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