show value based on criteria from 2 columns

[rickf19]

Hi All

I have a spreadsheet "sheet" that has data in col D Project, and col F Person
I want to show the value per person for each project in col G
This value resides in a sheet "Projects" where project is in coL C and the person is Cols S: AD
The value I require is find project no from "sheet" col D in "Projects" col C and show me the value for Person "sheet" col F from "Projects" col S:AD
EG Sheet D3 =P1402
Sheet F3 = AG
Project C2 = P1402
Project col S = AG
Value required = Project CELL S2
Can anyone tell me how to get that value, Projects cell S2 into sheet cell G3

Thanks

Rickf

 

[rondeondo]

Hi Rick,
Please include a sample including how you want it to work? I'm guessing you might need to add a column into your project sheet that includes the project name and person name in one cell then use a vlookup/xlookup to refer to that info from your sheet. Please use the L2BB link in the post to include your example

 

[rickf19]

Hi
work security doesnt allow files to be sent out so here are screenshots of relevant sheets and examples of required result on "sheet", the results are just + cell value from project sheet but would appreciate a more automated way if possible as there are a few spreadsheets like this with more data than this one has.

Thanks
Rick

 

[rondeondo]

I see how it is now, so you can use index() with a pair of match() in it,
in column G have something like in cell G2

=index(project!$c$1:$ad$1000,match($d2,project!$C$1:$c$1000,0),match($f2,project!$a$1:$ad$1000,0))

the extent of the range for the project sheet may need a little tweeking but it will be something like that.
So index() takes an array of data and is given a row and column reference number to specify the cell to return. The two match() functions are searching for the row and column respectivly.
They need to line up with the same number of rows and columns as the index. You'll need to make the ranges big enough to allow for expansion, or the formula will fail if your data gets bigger.

 

[Fluff]

How about

=INDEX(Projects!$S$2:$AD$1000,MATCH(D3,Projects!$C$2:$C$1000,0),MATCH(F3,Projects!$S$1:$AD$1,0))

 

[rickf19]

Thanks just tried it result attached, wants to verify where projects is when I run it ?
applies to both above formulas

Regards
Rick

 

[Fluff]

That formula will not work, try the one I posted.

 

[rondeondo]

you'll need to change the cell references, if projects sheet is in another file you'll need it open at the same time. I was guessing the cell refs based on the screen shots. Fluff's alterations should work.