CODEWITHSUNDEEP
excelexcel-formula
Simon Yo
Simon Yo

Reputation: 101

Match doesn't work on Specific String name

So I have a spreadsheet like this: enter image description here

I'm trying to run this formula to get the value from "Total Income" under the -Call Centre header

These are the values from the NAME_INPUT sheet:

B71 = A1:AJ39       D71 = E1:E39

This is the formula which is finding result 0:

=ROUND(INDEX(INDIRECT("'QB IS by class'!" & NAME_INPUT!B71),MATCH("Total Income",INDIRECT("'QB IS by class'!" & NAME_INPUT!D71)),MATCH("-Call Centre",'QB IS by class'!1:1,0))/1000,0)

I know the formula is written correctly, because if I change "Total Income" to "PIEFACE", it'll match and give me the correct value.

Why does the string, "Total Income" cause it to have zero results. I don't see any other fields under E column with the name, "Total Income". Even if I use, "Total Expense" that will get the correct value under -Call Centre for that row... So why does "Total Income" fail to give me a result?

The field types are all the same.

Thanks guys

Upvotes: 1

Views: 139

Answers (2)

A.S.H
A.S.H

Reputation: 29332

Although your data isn't clear, but I think you are doing an Exact Match so you forgot the third parameter 0 for the first MATCH in the formula:

MATCH("Total Income",INDIRECT("'QB IS by class'!" & NAME_INPUT!D71))

The above term should be 

MATCH("Total Income",INDIRECT("'QB IS by class'!" & NAME_INPUT!D71), 0)
                                                                   ^^^^

and the whole formula:

=ROUND(INDEX(INDIRECT("'QB IS by class'!" & NAME_INPUT!B71),MATCH("Total Income",INDIRECT("'QB IS by class'!" & NAME_INPUT!D71), 0),MATCH("-Call Centre",'QB IS by class'!1:1,0))/1000,0)

                                                                                                                                ^^^

Upvotes: 1

dwirony
dwirony

Reputation: 5450

Try substituting MATCH(CONCATENATE("*"&"Total Income"&"*")) in for MATCH("Total Income"

Upvotes: 0

Related Questions