Multiple Checks for IF - VLOOKUP not working

C

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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
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
Andrew Poulsom said:
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))
Click to expand...

Hi - does the final part of the formula work with the colon?
(i'm also trying to use adapt and use this)
 
Upvote 0
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
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
pavin said:
Check for the following thread, hope this helps:

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

Andrew Poulsom said:
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))
Click to expand...

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
Bubba said:
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
Click to expand...

If INDEX returns a reference like Sheet1!$A$5 the lookup range becomes Sheet1!$A$5:$B$10.
 
Upvote 0
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
You must log in or register to reply here.

Similar threads

R
  • Question
Multiple Criteria VLOOKUP Formula
2
Replies
15
Views
245
bertible
B
J
  • Solved
If(vlookup or formula
Replies
2
Views
165
Jon3sy
J
L
  • Locked
  • Question
Check box and Vlookup
Replies
9
Views
320
Fluff
Fluff
R
  • Question
VLOOKUP formula using the TEXT function as the "lookup value"
Replies
1
Views
677
awoohaw
awoohaw
T
  • Solved
Using IFNA with 2 Nested VLOOKUPs
Replies
5
Views
291
SanjayGMusafir
SanjayGMusafir

Forum statistics

Threads
1,214,978
Messages
6,122,547
Members
449,089
Latest member
davidcom

Share this page

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
Back
Top