Lookup across multiple sheets with 2 criteria

[TotallyLost]

Good afternoon.

I'm guessing this might be an indirect formula which I'm not sure how to do. I need to match two columns and if both columns match, return the value in the visits column. Please see the attached worksheet. I put in a tab called formula which shows what I need.

I would appreciate any help I can get. Thank you so much.

[Glenn Kennedy]

One way, with this array formula. if it does what you want, I'll happily explain it for you...

Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

In C4, copied down:
=IFERROR(INDEX(INDIRECT("'"&INDEX($H$1:$H$3,MATCH(TRUE,COUNTIF(INDIRECT("'"&$H$1:$H$3&"'!a3:a10"),A4)>0,0))&"'!c3:c10"),MATCH(A4,INDIRECT("'"&INDEX($H$1:$H$3,MATCH(TRUE,COUNTIF(INDIRECT("'"&$H$1:$H$3&"'!A3:A10"),A4)>0,0))&"'!A3:A10"),0)),"")

[TotallyLost]

Hi Glenn and thank you for answering my query.

I think I understand most of the formula, but it appears that it is only looking at the "customer" column for the match. I sometimes have repeating customer names but what is in the "Name" column is different. Is this formula matching both columns?

[Glenn Kennedy]

this will match on Name. it is (of course) possible to match on both, but the formula will be really horrible.

Does this meet your needs:

Please Login or Register  to view this content.