Index / Match with Multiple Criteria

[Gazsim]

Hi All,

I'm currently having some issues trying to build this formula despite all my limited best efforts. I've attached an example file but basically I need the formula in column D (sheet 1) to look up the origin in column B and Destination on column C (both on sheet 1) and also the week number in cell I2 (which would use a data validation list to change), and so therefore I can just change the week number and get the corresponding result from the table on sheet 2.

Many thanks in advance
Gaz

[Aardigspook]

You need an Index-Match-Match, where the first Match looks for a combination of Origin and Destination (concatenating them with the & symbol) to get the row and the second looks for the week number.

The basic formula would be this:

Please Login or Register  to view this content.

Important: this is an array formula so needs to be entered using Ctrl-Shift-Enter not just Enter. You will know the array is active when you see curly brackets { } appear around your formula - don't try to enter them yourself. If you do not Ctrl-Shift-Enter you will get an error or a clearly incorrect result. If that happens, just click into the formula bar and try again.


However, there are two problems which cause this formula to not work:
1. you have merged cells in column B on sheet1;
2. you have blank cells in column B on sheet2.

The best solution is this:
1. un-merge the cells and just repeat the Origin as necessary, giving BJS, BJS, BKK, BKK, etc;
2. fill in the blank cells to give the same effect.

There is no way round (2) - you will have to fill those cells in. You can use Conditional Formatting to make them appear blank if you want.

For (1), if you don't want to un-merge the cells and you will never have more than two rows for each origin on Sheet1, you can amend the formula to this:

Please Login or Register  to view this content.

Again, enter with Ctrl-Shift-Enter.

Where there is no answer (for example, there is no BJS-IE), you will get an error. You can hide this with IfError, like this:

Please Login or Register  to view this content.

Again, C-S-E to enter.


Attached is your file with that last formula and Conditional Formatting to half-hide the repeated Origins on Sheet2.

Hope that helps.

Edit: added colour to highlight parts of formulae

[Bo_Ry]

Please try at D3 and drag down

Please Login or Register  to view this content.

or if you fill blank Departure Port in sheet 2
D3
=IFERROR(1/(1/LOOKUP(1,1/(LOOKUP("z",B$3:B3)=Sheet2!$B$4:$B$23)/(C3=Sheet2!$C$4:$C$23),INDEX(Sheet2!$D$4:$O$23,,MATCH($I$2,Sheet2!$D$3:$O$3,)))),"")

[Gazsim]

Many thanks Aardigspook, that is a massive help!

[Gazsim]

Thanks also for the alternate solution Bo Ry! Much appreciated!

[Aardigspook]

You're welcome, glad we could help and thanks for the rep.

If that takes care of your original question, then please take a moment to mark the thread as Solved so others know there's an answer here (instructions are in my sig). Thanks.

[FlameRetired]

Agree with all said about merged cells, blank cells Origins.

This also works around that ... but it's a real mess.

Please Login or Register  to view this content.

[FlameRetired]

Drawing upon Bo's LOOKUP("z" approach ...

This is shorter and simpler than my last.

Please Login or Register  to view this content.