Help with Index/Match on two criteria

Hello folks,

I have a field that I need to populate from a datasheet containing computer or phone tags.

The formula I've tried to build looks up staff initials, then goes to find either if the device is phone or computer.

I'm getting odd results and have tried a few things but no joy.

Funny thing is one of the formulas works, the other not, yet only one parameter changes.

Sheet attached - the data will always be text string in this instance.

Any pointers are welcomed, thanks,

Ian

In F2

=INDEX(HARDWARE!$B$1:$B$45,MATCH(1,($A2=HARDWARE!$A$1:$A$45)*($B$1=HARDWARE!$C$1:$C$45),0))

in G2

=INDEX(HARDWARE!$B$1:$B$45,MATCH(1,($A2=HARDWARE!$A$1:$A$45)*($C$1=HARDWARE!$C$1:$C$45),0))

Both are array formulae


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Thanks - works fine in my test sheet but when copied and adapted to my actual sheet I get an n/a with 'a value is not available to the formula or function'

I even changed the row and spacing on my test sheet to ensure all column and row references were identical - and it works on test sheet but not on actual.

Usually a data error (Mismatch): can you post a file with error ?

As you have OS365, some (all?) formulas need not be array entered but I can't test this.

Edit - I think it's working - will roll out and feedback

Quick follow-up - being an array, is there any different way to add IFERROR to stop displays of N/A where the ID has yet to be added to a device?

IFERROR will work with or without arrays so just add in the usual way =IFERROR(formula,"")

Many thanks John