Help required with INDEX and multiple MATCH function - Excel 2007

In D2 and copy down, this ARRAY(confirm using control+shift+enter-NOT just Enter) formula.

Please Login or Register  to view this content.

Hi saundersben and welcome to the forum

i would do this with a helper column on your data sheet. in E2, copied down, use this...
=A2&B2&C2
then on both area sheets, use this in D2, copied down...
=IFERROR(INDEX(Database!$D$2:$E$11,MATCH(A2&B2&C2,Database!$E$2:$E$11,0),1),"Not Found")

let me know how you make out

Nice job, Fotis and FD... I should start using the '&' more with these types of formulas... call me old fashioned I guess... I used an array formula similar to Fotis' suggestion:

Formula:

Please Login or Register  to view this content.

- Moo

Here's a better explanation than I can give:

http://www.cpearson.com/excel/ArrayFormulas.aspx

- Moo

You are welcome.

See here an interesting article for Arrays Formulas.

http://www.cpearson.com/excel/arrayformulas.aspx

@ Moo

Thanks for your kind comment.

To return blank instead of an error (when there is no match of all 3 columns) with Fotis' formula, try this:

=IFERROR(INDEX(Database!$D$2:$D$11,MATCH(A2&B2&C2,Database!$A$2:$A$11&Database!$B$2:$B$11&Database!$C$2:$C$11,0)),"")

Again, entered with Ctrl + Shift + Enter

- Moo

Originally Posted by saundersben50

Thanks I'll have a read through. One last question, I promise, how would I make Fotis' formula return a blank cell instead of an error should the formula return false, i.e. if the 3 cells cannot be matched on the database?

Feel free to ask any question.

thanks for the thanks Moo, i need to use arrays more too lol. call me old fashioned, but i still like my helper columns

I am fun of the helper columns but unfortunnately the majority of people don't like these!

http://www.excelforum.com/the-water-...r-columns.html