Lookup Help

[SDowse]

Hi,

I am trying to do a lookup with multiple criteria, but struggling. Found a lot of Index/Match ideas, but I don't think they work. Reason being I am not looking up multiple values in the similar ranges.

What I am trying to do is look up the value from Sheet1!A4 in Sheet2!C4:M4, then the value from Sheet1!A4 in Sheet2!C5:M5, then the value from Sheet1!D4 in Sheet2!B7:B29....and then based on the findings return the relative value from Sheet2!C7:M29.

I hope, but doubt, this is clear...so any help would be greatly appreciated.

Thanks in advance.

 

[Andrew Poulsom]

I don't undersstand why you need to lookup the same cell twice:

What I am trying to do is look up the value from Sheet1!A4 in Sheet2!C4:M4, then the value from Sheet1!A4 in Sheet2!C5:M5

Maybe this will help you:

http://www.contextures.com/xlFunctions03.html#IndexMatch2

 

[SDowse]

Sorry, the 2nd value should have been looking up the value from Sheet1!B4 in Sheet2!C5:M5.

Will take a look at the link, but let me know if the amended range changes anything.

 

[Andrew Poulsom]

What's in Sheet2!C4:M4 and Sheet2!C5:M5 - can you post a snapshot?

 

[SDowse]

Right, well using the link provided I came up with:

=INDEX('Planned Data'!$C$7:$ZZ$29,MATCH(GANT!A4,'Planned Data'!C$4:ZZ$4,0),MATCH(GANT!B4,'Planned Data'!C$5:ZZ$5,0),MATCH(GANT!$D4,'Planned Data'!$B$7:$B$29,0))

This seems to work for the first row only though. The formula on the next row is:

=INDEX('Planned Data'!$C$7:$ZZ$29,MATCH(GANT!A4,'Planned Data'!C$4:ZZ$4,0),MATCH(GANT!B4,'Planned Data'!C$5:ZZ$5,0),MATCH(GANT!$D5,'Planned Data'!$B$7:$B$29,0))

Which returns #REF!. I thought it might be because it is looking up in A4 and B4 but then D5, but I have changed my spreadsheet to test this and it made no difference. Any ideas?

 

[Andrew Poulsom]

Please post a snapshot of what's in C4:M5 on sheet Planned Data.

 

[SDowse]

Here is a sample snapshot, doesn't go all the way to M, but covers down to row 9.
<TABLE style="WIDTH: 556pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=740><COLGROUP><COL style="WIDTH: 139pt; mso-width-source: userset; mso-width-alt: 7893" span=4 width=185><TBODY><TR style="HEIGHT: 38.25pt" height=51><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8 0.5pt; BACKGROUND-COLOR: #bfbfbf; WIDTH: 139pt; HEIGHT: 38.25pt; BORDER-TOP: #d4d0c8 0.5pt; BORDER-RIGHT: #d4d0c8 0.5pt" class=xl69 height=51 width=185>Project A</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #bfbfbf; WIDTH: 139pt; BORDER-TOP: #d4d0c8 0.5pt; BORDER-RIGHT: #d4d0c8 0.5pt" class=xl69 width=185>Project B</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #bfbfbf; WIDTH: 139pt; BORDER-TOP: #d4d0c8 0.5pt; BORDER-RIGHT: #d4d0c8 0.5pt" class=xl69 width=185>Project B</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #bfbfbf; WIDTH: 139pt; BORDER-TOP: #d4d0c8 0.5pt; BORDER-RIGHT: #d4d0c8 0.5pt" class=xl69 width=185>Project C</TD></TR><TR style="HEIGHT: 13.5pt; mso-height-source: userset" height=18><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 139pt; HEIGHT: 13.5pt; BORDER-TOP: #d4d0c8 0.5pt; BORDER-RIGHT: #d4d0c8 0.5pt" class=xl68 height=18 width=185>N/A</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 139pt; BORDER-TOP: #d4d0c8 0.5pt; BORDER-RIGHT: #d4d0c8 0.5pt" class=xl68 width=185>A</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 139pt; BORDER-TOP: #d4d0c8 0.5pt; BORDER-RIGHT: #d4d0c8 0.5pt" class=xl68 width=185>B</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 139pt; BORDER-TOP: #d4d0c8 0.5pt; BORDER-RIGHT: #d4d0c8 0.5pt" class=xl68 width=185>N/A</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: black; WIDTH: 139pt; HEIGHT: 12pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8 0.5pt" class=xl65 height=16 width=185>Week Ending</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: black; WIDTH: 139pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8 0.5pt" class=xl65 width=185>Week Ending</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: black; WIDTH: 139pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8 0.5pt" class=xl65 width=185>Week Ending</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: black; WIDTH: 139pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8 0.5pt" class=xl65 width=185>Week Ending</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt; BORDER-LEFT: #d4d0c8 0.5pt; BACKGROUND-COLOR: transparent; WIDTH: 139pt; HEIGHT: 12pt; BORDER-TOP: #d4d0c8 0.5pt; BORDER-RIGHT: #d4d0c8 0.5pt" class=xl63 height=16 width=185>To Be Determined</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 139pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #d4d0c8 0.5pt" class=xl67 width=185 align=right>03/06/2011</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 139pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #d4d0c8 0.5pt" class=xl67 width=185><Select from drop down list></TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 139pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #d4d0c8 0.5pt" class=xl67 width=185><Select from drop down list></TD></TR><TR style="HEIGHT: 24pt" height=32><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt; BORDER-LEFT: #d4d0c8 0.5pt; BACKGROUND-COLOR: transparent; WIDTH: 139pt; HEIGHT: 24pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8 0.5pt" class=xl63 height=32 width=185>To Be Determined</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 139pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8 0.5pt" class=xl63 width=185 align=right>03/06/2011</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 139pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8 0.5pt" class=xl63 width=185><Select from drop down list></TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 139pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8 0.5pt" class=xl63 width=185><Select from drop down list></TD></TR><TR style="HEIGHT: 24pt" height=32><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt; BORDER-LEFT: #d4d0c8 0.5pt; BACKGROUND-COLOR: transparent; WIDTH: 139pt; HEIGHT: 24pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8 0.5pt" class=xl63 height=32 width=185>To Be Determined</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 139pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8 0.5pt" class=xl63 width=185 align=right>03/06/2011</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 139pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8 0.5pt" class=xl63 width=185><Select from drop down list></TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 139pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8 0.5pt" class=xl63 width=185><Select from drop down list></TD></TR></TBODY></TABLE>

So Project A, Project B etc...are in row 4. I need to look at row 4 and 5 because project name may be repeated in row 5 in which case I need to also match what is in row 5 (which will be a different phase of project).

Hope that helps. I am wanting to pick the data from rows 8 down for each project (the dates).

 

[SDowse]

Sorry, project name will be repeated in row 4, with different phases in row 5.

 

[SDowse]

Any ideas?

 

[SDowse]

Sorted...the below formula seems to work...

=INDEX('Planned Data'!$C$7:$ZZ$29,MATCH(GANT!D2,'Planned Data'!$B$7:$B$29,0),MATCH(1,('Planned Data'!C$4:ZZ$4=GANT!$A$2)*('Planned Data'!C$5:ZZ$5=GANT!$B$2),0))


Thanks for the help received!