lookup formula that evaluates two criteria

**waternut** · 09-02-2011, 08:17 AM

Hi all,

I am trying to help a friend with her worksheet sheet and what she is looking to do (see attached) is by selecting an organization on sheet data test from the dropdown list in cell B2 is that the formula placed in all the purple cells below will recognize what org has been selected look at the project name that has been selected from column A and then use those two selections to go to sheet monthly data and grab the appropriate cells data.

In my test case for the formula in B13 it would find org "AA" selected from the dropdown and would need to find the org's AA data for "project number one A" from the monthly data sheet, with the correct data being 12.7 placed in cell B13.

She needs the formula to be dynamic so that whatevevr org is selected it will pull in and populate those purple cells.

As always any help is appreciated.

Thx.

Chris

**NBVC** · 09-02-2011, 08:21 AM

Try:

=INDEX('monthly data'!$A$1:$V$46,MATCH(A13,'monthly data'!$A$1:$A$46,0),MATCH($B$1,'monthly data'!$A$1:$V$1,0))

copied down, when you stop at B19, you can copy B19, paste to B21, and copy down, and repeat for each purple block.

**waternut** · 09-02-2011, 08:56 AM

Thank you very much!!!! For my own knowledge can you tell me what the formula is actually doing? I am not familiar with INDEX & MATCH?

Thanks again!

Chris