| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return value
I am working on a sheet with the following: Sheet 1 Col A = Description Sheet 1 Col B = ID Sheet 1 Col C = Returned value from Sheet 2 Col B when value in Sheet 1 Col B matches value in Sheet 2 Col A I would like to take a cell from Sheet 1 Col B and find the matching value in Sheet 2 Col A and fill in Sheet 1 Col C with associated value from Sheet 2 Col B. I know this is a simple lookup but I can't get it to work. -- DebP ------------------------------------------------------------------------ DebP's Profile: http://www.excelforum.com/member.php...o&userid=32273 View this thread: http://www.excelforum.com/showthread...hreadid=520262 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return value
Hi!
Try this: =IF(COUNTIF(B1,Sheet2!A:A),VLOOKUP(B1,Sheet2!A:B,2 ,0),"") Biff "DebP" wrote in message ... I am working on a sheet with the following: Sheet 1 Col A = Description Sheet 1 Col B = ID Sheet 1 Col C = Returned value from Sheet 2 Col B when value in Sheet 1 Col B matches value in Sheet 2 Col A I would like to take a cell from Sheet 1 Col B and find the matching value in Sheet 2 Col A and fill in Sheet 1 Col C with associated value from Sheet 2 Col B. I know this is a simple lookup but I can't get it to work. -- DebP ------------------------------------------------------------------------ DebP's Profile: http://www.excelforum.com/member.php...o&userid=32273 View this thread: http://www.excelforum.com/showthread...hreadid=520262 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return value
Thanks for the help, but I tried your suggestion and nothing happened. -- DebP ------------------------------------------------------------------------ DebP's Profile: http://www.excelforum.com/member.php...o&userid=32273 View this thread: http://www.excelforum.com/showthread...hreadid=520262 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return value
Thanks for the help, but I tried your suggestion and nothing happened.
Ok, let's examine what that means! =IF(COUNTIF(B1,Sheet2!A:A),VLOOKUP(B1,Sheet2!A:B, 2,0),"") I take it that "nothing happened" means the formula returned a blank which is what the "" is for. So, if the formula returned a blank that means this portion: COUNTIF(B1,Sheet2!A:A) evaluated to zero which means that there is no match of B1 on Sheet2 in column A. If you can see for "certain" that there is in fact a match try this: Find the matching cell on Sheet2 of column A. Assume that cell is A40. Enter this formula in some empty cell: =Sheet1!B1=Sheet2!A40 If they are in fact matches that formula will return TRUE and the lookup formula should have returned the appropriate corresponding value. If the formula returns FALSE then they are not matches. They may look the same but can still not be matches. One value may have leading or trailing spaces that you can't see. One value may be a TEXT number and the other may be a NUMERIC number. Biff "DebP" wrote in message ... Thanks for the help, but I tried your suggestion and nothing happened. -- DebP ------------------------------------------------------------------------ DebP's Profile: http://www.excelforum.com/member.php...o&userid=32273 View this thread: http://www.excelforum.com/showthread...hreadid=520262 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return value
Ooops!
I committed a major blunder! I'm downright embarrassed! =IF(COUNTIF(B1,Sheet2!A:A),VLOOKUP(B1,Sheet2!A:B,2 ,0),"") I have the arguments backwards in the Countif function!!!!!!! <ouch Should be: =IF(COUNTIF(Sheet2!A:A,B1),VLOOKUP(B1,Sheet2!A:B,2 ,0),"") Biff "Biff" wrote in message ... Thanks for the help, but I tried your suggestion and nothing happened. Ok, let's examine what that means! =IF(COUNTIF(B1,Sheet2!A:A),VLOOKUP(B1,Sheet2!A:B ,2,0),"") I take it that "nothing happened" means the formula returned a blank which is what the "" is for. So, if the formula returned a blank that means this portion: COUNTIF(B1,Sheet2!A:A) evaluated to zero which means that there is no match of B1 on Sheet2 in column A. If you can see for "certain" that there is in fact a match try this: Find the matching cell on Sheet2 of column A. Assume that cell is A40. Enter this formula in some empty cell: =Sheet1!B1=Sheet2!A40 If they are in fact matches that formula will return TRUE and the lookup formula should have returned the appropriate corresponding value. If the formula returns FALSE then they are not matches. They may look the same but can still not be matches. One value may have leading or trailing spaces that you can't see. One value may be a TEXT number and the other may be a NUMERIC number. Biff "DebP" wrote in message ... Thanks for the help, but I tried your suggestion and nothing happened. -- DebP ------------------------------------------------------------------------ DebP's Profile: http://www.excelforum.com/member.php...o&userid=32273 View this thread: http://www.excelforum.com/showthread...hreadid=520262 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
| Huge problem with "if" formula's | Excel Discussion (Misc queries) | |||
| Excel - return a picture or range rows as the result of a formula | Excel Worksheet Functions | |||
| How do create a formula to evalute a # to return 1 of 4 conditions | Excel Worksheet Functions | |||
| if the value of a cell in a range is not blank, then return the v. | Excel Worksheet Functions | |||
Linear Mode
