Multiple Checks for IF - VLOOKUP not working

[cheekbones3]

Hello, firstly, I've searched through a lot of previous posts regarding VLOOKUP, but most of them appear to be looking multiple data returns. While I have a similar problem, I'm only ever looking for one match for each value, but sometimes there are more than one, and I need the second.

The way I've tried to get round the problem is by embedding the following code:

(VLOOKUP(A2,[svdlsa08_2007mast.xls]Sheet1!$A$2:$U$3869,1,FALSE)

in the following statement:

=IF(((VLOOKUP(A2,[svdlsa08_2007mast.xls]Sheet1!$A$2:$U$3869,1,FALSE))-A2=0),(VLOOKUP(C2,[svdlsa08_2007mast.xls]Sheet1!$C$2:$U$3869,17,FALSE)),"No Match")

The IF statement is designed to check a different value in the source spreadsheet against one in the destination sheet which should always match for the desired values, but this doesn't seem to make any difference to what is being returned - I'm still getting the first match instead of the appropriate one.

Is there a better way to force the VLOOKUP to return the value from the correct match and not just the first one?

Cheers,
Ian

 

[Bubba]

So the section in bold Sheet1!$B$10) should be the bottom right hand corner of the array?

(i can't say i understand the logic but it seems to work for me!)

 

[Andrew Poulsom]

You are missing [svdlsa08_2007mast.xls] in quite a few places.

 

[Andrew Poulsom]

So the section in bold Sheet1!$B$10) should be the bottom right hand corner of the array?

(i can't say i understand the logic but it seems to work for me!)

Yes that's right. It's the same as in the lookup of the first item:

VLOOKUP(A2,Sheet1!$A$2:$B$10,2,FALSE)

 

[cheekbones3]

You are missing [svdlsa08_2007mast.xls] in quite a few places.

Aha! I misunderstood how the references were working.

It works now, but the opposite problem now occurs. How do I get the first value of the repeating pair to refer to the first value (which is actually correct) while the second occurrence remains fixed is it is now?

 

[Andrew Poulsom]

Aha! I misunderstood how the references were working.

It works now, but the opposite problem now occurs. How do I get the first value of the repeating pair to refer to the first value (which is actually correct) while the second occurrence remains fixed is it is now?

Sorry, I don't understand what you mean.

 

[Bubba]

Sorry, I don't understand what you mean.

I think I know - you need to change the =1 to =2 (assuming there are two instances)

=IF(COUNTIF(Sheet1!$A$2:$A$10,A2)=1,VLOOKUP(A2,Sheet1!$A$2:$B$10,2,FALSE),VLOOKUP(A2,INDEX(Sheet1!$A$2:$A$10,MATCH(A2,Sh eet1!$A$2:$A$10,FALSE)+1):Sheet1!$B$10,2,FALSE))

 

[cheekbones3]

Sorry, I don't understand what you mean.

I'm propagating this formula down 4,000 rows, so I've now realised that it works okay where there are duplicates and I'm looking for the second match, but it now gives the wrong value for the first duplicate.

I think I know - you need to change the =1 to =2 (assuming there are two instances)

=IF(COUNTIF(Sheet1!$A$2:$A$10,A2)=1,VLOOKUP(A2,Sheet1!$A$2:$B$10,2,FALSE),VLOOKUP(A2,INDEX(Sheet1!$A$2:$A$10,MATCH(A2,Sh eet1!$A$2:$A$10,FALSE)+1):Sheet1!$B$10,2,FALSE))

I don't fancy doing this manually for a few hundred entries though

 

[Andrew Poulsom]

I'm propagating this formula down 4,000 rows, so I've now realised that it works okay where there are duplicates and I'm looking for the second match, but it now gives the wrong value for the first duplicate.

Sorry I still don't understand. If there is only one value my formula returns it. If there is more than one value it returns the second. That's what you asked for in your original post. Are you now saying you want something different?

 

[cheekbones3]

Sorry I still don't understand. If there is only one value my formula returns it. If there is more than one value it returns the second. That's what you asked for in your original post. Are you now saying you want something different?

Ah I see. The formula doesn't appear to be working like this. In every case the formula is returning the second value where there is more than one.

 

[Andrew Poulsom]

Yes, the formula returns the second value if there is more than one. Isn't that what you asked for in your first post?