Index and Match for 2No criteria only partially returning values other values #ref error.

[adammikejones]

Hi,

I'm trying to use a index and match function using 2No critiera to provide data. The functions works for some of the criteria but not for others (#ref error). The one of the formulae is:-
=INDEX('Lookup table'!$C$7:$G$48, MATCH(1,($H73, 'Lookup table'!$D$7:$D$48), 0) * MATCH($B73, 'Lookup table'!$E$7:$E$48, 0),4)

The match functions are looking for text in both instances and index returning a number value. I have verified all formating, types etc.

 

[StephenCrump]

Welcome to the Forum!

Did you mean

=INDEX('Lookup Table'!$C$7:$G$48, MATCH(1,($H73='Lookup Table'!$D$7:$D$48)*($B73='Lookup Table'!$E$7:$E$48),),4)

(which will find only the first match)

 

[roelandwatteeuw]

Hi

It's not really clear without more information.
Can you give us the data or a printscreen?

What I can say already.
If you want to use 2 (or more) criteria, make an extra helper column in both your Lookup table and for your criteria you want to look for;

Helper column for the criteria you want to look for:

=$H73 & "-" & $B73

Helper column for the Lookup table:

=D7 & "-" & E7

Than use a index match on those new helper columns.

grtz

 

[adammikejones]

Hi,

Thanks for the quick responses. I have provided screenshots below:-

Look-up table (grey) and light blue and green drop-down list references to other sheet:-

An example below of the outputs format using drop down lists and then index/match function for money in and money out. The below example is a cell with a #ref error.

The next example is a cell with the same function with no error generated. The #ref error appears to be generated for the rows in the look-up table as follows 22-24, 26-30, 31-36, 38-42, 44-48.

 

[roelandwatteeuw]

Try StephenCrump's suggestion. Should work

 

[adammikejones]

Thanks this has now been solved. I would appreciate it if someone could explain to me the error in my previous formula.

 

[roelandwatteeuw]

In your screenshot:
You multiply the rows of the first found values.
For row 92:
1. Looks for '60k gross' --> First found in the 6th row of the lookup table
2. Looks for 'A3 scenario' --> First found in the 13th row of the lookup table
3. You multiply them: 6 * 13 = 78
4. Searching in your lookup table for the 78th row --> Doesn't exist --> Error


In your formula from the first post:
You've kind off up mixed up the formula's, which StephenCrump corrected.
You did:
1. give me an exact match with the result of 1
2. and then you give as lookup-matrix 2 ranges --> H73 and the lookuptable --> Can't do that --> Error


Hope this clears out