I have a spread sheet where I am trying to use a index and 2 matches to return data from another cell. I keep getting a # ref error and don't understand why. I've tried changing the values to General, text, numbers and still can't figure it out.
I have a spread sheet where I am trying to use a index and 2 matches to return data from another cell. I keep getting a # ref error and don't understand why. I've tried changing the values to General, text, numbers and still can't figure it out.
The second MATCH function is to return a Column reference to INDEX, not a Row reference. The formula in H9 doesn't work correctly either, it just gives the correct result by chance.
To return the row where multiple columns match, you need a different approach, for example
Formula:=INDEX(Sheet2!$E$6:$E$7,INDEX(MATCH(2,1/(Sheet1!F9=Sheet2!$C$6:$C$7)*(Sheet1!G9=Sheet2!$D$6:$D$7)),))
One way:committed with Ctrl-Shift-Enter rather than just EnterFormula:=INDEX(Sheet2!$E$5:$E$7,MATCH(Sheet1!F9&Sheet1!G9,Sheet2!$C$5:$C$7&Sheet2!$D$5:$D$7,0))
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
try into H9:
Formula:=INDEX(Sheet2!$E$6:$E$7,MATCH(1,(Sheet1!F9=Sheet2!$C$6:$C$7)*(Sheet1!G9=Sheet2!$D$6:$D$7),0))
accept with Ctrl+Shift+Enter and drag it down
Happy with my answer * Add Reputation.
If You are happy with solution, please use Thread tools and mark thread as SOLVED.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks