If with Vlookup

[Looping through]

I need to compare a cell in a workbook against a range of cell in a different
book. Sheet1 A2 against sheet 2 E6:E1000, If a match is found I want to
vlookup a cell in Sheet2, if not found return 0.

When I try to wite this formula, I get an error. My Vlookup work by itself,
so I think it has to do with my IF statement.

Any suggestions
Thanks
Peter

 

[Carim]

Hi,

Are you dealing with two worksheets or workbooks ...?

If you are talking about workbooks, are they opened or closed ...?

 

[Stephen]

Please, please, please, post your formula!

 

[Looping through]

They are "workbooks" and only only the one with this formula will be open. Do
they both need to be open together?

Thanks
Peter

 

[Carim]

Hi,

Well you can give it a try, I am pretty sure your formula works fine
when both workbooks are opened ...?

Yes or No ...?

 

[Looping through]

=if(A2='Y:\Backlog\[Backlog Category Rankings.xls]Backlog
Detail'!$E$6:$E$1000,VLOOKUP(A3,'Y:\Backlog\[Backlog Category
Rankings.xls]Backlog Detail'!$E$6:$Z$404,8),"")

Peter

 

[Looping through]

No, I still get the error with both workbooks open.

 

[Carim]

Well, your ranges are OK, it could be that you are using the
alphabetic order in your reference table ...
and that you should replace lookup() by a combination of index() and
match()

 

[Carim]

Well, if you have checked your ranges and they are OK ...
it could be that there is no alphabetic order in your reference
table ...

If it is the case, you should replace lookup() by a combination of
index() and
match() ...

HTH

 

[Stephen]

Right, well A2 cannot equal 'Y:\Backlog\[Backlog Category
Rankings.xls]Backlog Detail'!$E$6:$E$1000.
A2 is a single value, the other is an array of 995 values!
You need a MATCH function to see if A2 appears in the array (this returns
the position in the array, or #N/A), and then ISNUMBER to decode what it
returns:
=IF(ISNUMBER(MATCH(A2,'Y:\Backlog\[Backlog Category Rankings.xls]Backlog
Detail'!$E$6:$E$1000,0)),VLOOKUP... etc.

=if(A2='Y:\Backlog\[Backlog Category Rankings.xls]Backlog
Detail'!$E$6:$E$1000,VLOOKUP(A3,'Y:\Backlog\[Backlog Category
Rankings.xls]Backlog Detail'!$E$6:$Z$404,8),"")

Peter

Please, please, please, post your formula!

 

[Carim]

Try following first :

=if(A2='Y:\Backlog\[Backlog Category Rankings.xls]Backlog
Detail'!E6,VLOOKUP(A2,'Y:\Backlog\[Backlog Category
Rankings.xls]Backlog Detail'!$E$6:$Z$404,8),"")

HTH