Multiple Checks for IF - VLOOKUP not working

cheekbones3

Board Regular
Joined
Jun 13, 2007
Messages
85
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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Below is an example formula that returns the second value if there are more than one:

=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,Sheet1!$A$2:$A$10,FALSE)+1):Sheet1!$B$10,2,FALSE))
 
Upvote 0

Bubba

Board Regular
Joined
May 1, 2002
Messages
73
Below is an example formula that returns the second value if there are more than one:

=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,Sheet1!$A$2:$A$10,FALSE)+1):Sheet1!$B$10,2,FALSE))

Hi - does the final part of the formula work with the colon?
(i'm also trying to use adapt and use this)
 
Upvote 0

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
I tested the formula before I posted it and it does work (INDEX is returning a reference like Sheet1!$A$5).

I'm not sure how the space got into Sh eet1!, so I'll post again:

Code:
=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,Sheet1!$A$2:$A$10,FALSE)+1):Sheet1!$B$10,2,FALSE))
 
Upvote 0

Bubba

Board Regular
Joined
May 1, 2002
Messages
73
Sorry to hijack!

But could someone please explain to me the relevance of the clause in bold below

=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,Sheet1!$A$2:$A$10,FALSE)+1):Sheet1!$B$10,2,FALSE))

I think i may be able to adapt it as this may be the answer to my thread: http://www.mrexcel.com/forum/showthread.php?t=375511
 
Upvote 0

cheekbones3

Board Regular
Joined
Jun 13, 2007
Messages
85
Check for the following thread, hope this helps:

http://www.mrexcel.com/forum/showthread.php?t=11137

I tested the formula before I posted it and it does work (INDEX is returning a reference like Sheet1!$A$5).

I'm not sure how the space got into Sh eet1!, so I'll post again:

Code:
=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,Sheet1!$A$2:$A$10,FALSE)+1):Sheet1!$B$10,2,FALSE))

Thanks for the link Pavin, although I tried making a unique ID and it didn't work, I'm not sure why.

Andrew, your method appears more promising (i.e. it works in the cases where there is no repetition), although whenever there is a repeated value, I get a #VALUE! error, and I can't work out why. Can you think of a reason for this?
 
Upvote 0

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Sorry to hijack!

But could someone please explain to me the relevance of the clause in bold below

=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,Sheet1!$A$2:$A$10,FALSE)+1):Sheet1!$B$10,2,FALSE))

I think i may be able to adapt it as this may be the answer to my thread: http://www.mrexcel.com/forum/showthread.php?t=375511

If INDEX returns a reference like Sheet1!$A$5 the lookup range becomes Sheet1!$A$5:$B$10.
 
Upvote 0

cheekbones3

Board Regular
Joined
Jun 13, 2007
Messages
85
I'm getting it for item one as well, which may give more of a clue to the problem.

Here is the adapted code:

=IF(COUNTIF(Sheet1!$C$2:$C$3931,C2)=1,VLOOKUP(C2,[svdlsa08_2007mast.xls]Sheet1!$C$2:$U$3931,18,FALSE),VLOOKUP(C2,INDEX([svdlsa08_2007mast.xls]Sheet1!$C$2:$C$3931,MATCH(C2,Sheet1!$C$2:$C$3931,FALSE)+1):Sheet1!$U$3931,18,FALSE))
 
Upvote 0

Forum statistics

Threads
1,190,694
Messages
5,982,337
Members
439,774
Latest member
InfinityMrsn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top