Help with Index / Match / Nested If Statement 1

[killer23]

Hello All -

The following formula works but it does not work perfectly:

INDEX(GMSL!$W$2:$W$5000,MATCH(A4,IF(GMSL!$H$2:$H$5000="Y",GMSL!$A$2:$A$5000,"Blank"),0))

However, instead of returning "Blank" if the data row is null or not a match, it returns 0. Even if I remove "Blank" is still get the same results What Am I missing here.

For clarification the "GMSL" spreadsheet contain data about locations for a global company (Location ID, name, address, etc.) A4 in the formula reference the location ID, while GMSL colum H is a yes / no field, and GMSL colum W has the data I want to find if there is a match on the Location ID and Yes.

Thanks in advance for the help and please don't beat me up too badly for incorrect coding (I'm not a programmer).

 

[SkipVought]

Hi,

Please post a small sample of your table. Please supply the LOOKUP value represented in your formula, consistent with your sample data. Make the sample table representative of the problem.

Your MATCH needs an range returned from the IF statement, not "BLANK"!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

INDEX returns ONE value. What data do you want returned, as it seems that column H contains LOTS of Ys.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[killer23]

Hi Skip -

LocID Primary Y/N Business Unit

ABC1 Y GOV
ABC1 N USMC
FOR6 Y USN
FOR8 Y USAF
FOR10 N USAF
FOR10 Y USMC

The data is sequencial by LocID - I need to match the LocID, then Y, and return the Business unit. If there is no match on the LocId or there is not a Y (one to one) on the LocID it should return something like "not found" or "does not exist".

Thanks for your help

 

[killer23]

There is only one Y per LocID

 

[SkipVought]

[tt]=INDEX(OFFSET(A1,MATCH(E2,LocID,0),2,COUNTIF(LocID,E2),1),MATCH("Y",OFFSET(A1,MATCH(E2,LocID,0),1,COUNTIF(LocID,E2),1),0),1)
[/tt]
where

1. Using Named Ranges, based on your headings
2. A1 is the top-left cell in your table
3. E2 is a lookup value, like FOR10.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[killer23]

Okay - almost there. The column counts are off in my "sample".

LocID = Column 1
Primary Y/N = Column 8
Business Unit = Colum 2

Sorry about confusing things.

 

[SkipVought]

[tt]
=INDEX(OFFSET(A1,MATCH(E2,LocID,0),b]2-1[/b],COUNTIF(LocID,E2),1),MATCH("Y",OFFSET(A1,MATCH(E2,LocID,0),8-1,COUNTIF(LocID,E2),1),0),1)
[/tt]
Hint:

Excel HELP on OFFSET, INDEX , MATCH, COUNTIF

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[killer23]

Good to go - thanks skip - I'll give it a go.

 

[SkipVought]

oops...
[tt]
=INDEX(OFFSET(A1,MATCH(E2,LocID,0),2-1,COUNTIF(LocID,E2),1),MATCH("Y",OFFSET(A1,MATCH(E2,LocID,0),8-1,COUNTIF(LocID,E2),1),0),1)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}