Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If with Vlookup
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If with Vlookup
Hi,
Are you dealing with two worksheets or workbooks ...? If you are talking about workbooks, are they opened or closed ...? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If with Vlookup
Please, please, please, post your formula!
"Looping through" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If with Vlookup
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" wrote: Hi, Are you dealing with two worksheets or workbooks ...? If you are talking about workbooks, are they opened or closed ...? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If with Vlookup
Hi,
Well you can give it a try, I am pretty sure your formula works fine when both workbooks are opened ...? Yes or No ...? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If with Vlookup
=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 "Stephen" wrote: Please, please, please, post your formula! "Looping through" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If with Vlookup
No, I still get the error with both workbooks open.
"Carim" wrote: Hi, Well you can give it a try, I am pretty sure your formula works fine when both workbooks are opened ...? Yes or No ...? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If with Vlookup
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() |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If with Vlookup
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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If with Vlookup
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. "Looping through" wrote in message ... =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 "Stephen" wrote: Please, please, please, post your formula! "Looping through" wrote in message ... 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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If with Vlookup
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
IF(AND(val1=VLOOKUP( );val2>=VLOOKUP( );val2<=VLOOKUP( );VLOOKUP( | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |