Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP Formulas
I'm not getting the desired result using VLOOKUP. I have a multiple page
workbook. In the first page (where my formula lives), Column I contains a text string (such as STRQ321). In the second workpage, Column A contains text strings ranging from STRQ1 to STRQ601. Columns B and D contain information I want to have copied into the first workpage. I'm doing a conditional lookup (if the cell in column L says "Y", I do the lookup, otherwise I enter the text "N/A" in the cell). My equation is as follows: =IF((L6="N"), "N/A", VLOOKUP(I6,'STRQ Req Summary'!A:F,2,FALSE)) Excel correctly evaluates I6 as the string "STRQ321". When it goes to the referenced page VLOOKUP comes back with an error (#N/A). If I change FALSE to TRUE, VLOOKUP returns the information from the line with "STRQ320". I temporarily changed the return information from VLOOKUP to be the information contained in Column A of the second worksheet -- this means that I should have the same value from the lookup as is in I6, however I get #N/A when I have FALSE and STRQ320 when I have TRUE. I have changed the type of data in both columns to "Text", "Number" and "General" with no change in the results. I have sorted the data in the second sheet, again with no change in the results. This result happens for every entry within the worksheet as I attempt to do lookups of data. Any ideas? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP Formulas
Gayle,
It sounds like you might have a space after each of the values in Column A on the 2nd page. What would then happen is that FALSE will not find an exact match (because of the space). However TRUE will go down the list until Column A is alphabetically higher and then give the last value that was not higher. "Gayle B" wrote: I'm not getting the desired result using VLOOKUP. I have a multiple page workbook. In the first page (where my formula lives), Column I contains a text string (such as STRQ321). In the second workpage, Column A contains text strings ranging from STRQ1 to STRQ601. Columns B and D contain information I want to have copied into the first workpage. I'm doing a conditional lookup (if the cell in column L says "Y", I do the lookup, otherwise I enter the text "N/A" in the cell). My equation is as follows: =IF((L6="N"), "N/A", VLOOKUP(I6,'STRQ Req Summary'!A:F,2,FALSE)) Excel correctly evaluates I6 as the string "STRQ321". When it goes to the referenced page VLOOKUP comes back with an error (#N/A). If I change FALSE to TRUE, VLOOKUP returns the information from the line with "STRQ320". I temporarily changed the return information from VLOOKUP to be the information contained in Column A of the second worksheet -- this means that I should have the same value from the lookup as is in I6, however I get #N/A when I have FALSE and STRQ320 when I have TRUE. I have changed the type of data in both columns to "Text", "Number" and "General" with no change in the results. I have sorted the data in the second sheet, again with no change in the results. This result happens for every entry within the worksheet as I attempt to do lookups of data. Any ideas? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP Formulas
Thank you George and Art. The second workpage did have trailing spaces after
the text strings. The problem is now solved. "George Nicholson" wrote: Just a guess: Look for a trailing space(s) after your entries (in either, but not both, lists). A trailing space wouldn't change the sort order, but it could prevent an exact match (#N/A when FALSE/Exact and the 'previous' list value when TRUE/Approximate). HTH, "Gayle B" <Gayle wrote in message ... I'm not getting the desired result using VLOOKUP. I have a multiple page workbook. In the first page (where my formula lives), Column I contains a text string (such as STRQ321). In the second workpage, Column A contains text strings ranging from STRQ1 to STRQ601. Columns B and D contain information I want to have copied into the first workpage. I'm doing a conditional lookup (if the cell in column L says "Y", I do the lookup, otherwise I enter the text "N/A" in the cell). My equation is as follows: =IF((L6="N"), "N/A", VLOOKUP(I6,'STRQ Req Summary'!A:F,2,FALSE)) Excel correctly evaluates I6 as the string "STRQ321". When it goes to the referenced page VLOOKUP comes back with an error (#N/A). If I change FALSE to TRUE, VLOOKUP returns the information from the line with "STRQ320". I temporarily changed the return information from VLOOKUP to be the information contained in Column A of the second worksheet -- this means that I should have the same value from the lookup as is in I6, however I get #N/A when I have FALSE and STRQ320 when I have TRUE. I have changed the type of data in both columns to "Text", "Number" and "General" with no change in the results. I have sorted the data in the second sheet, again with no change in the results. This result happens for every entry within the worksheet as I attempt to do lookups of data. Any ideas? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP Formulas
Gayle, I have something similar happening and don't find any spaces. I'm
brand new at doing IF and/or Lookup formulas. I'm trying to analyze yours to see if it helps this greenie at all. I think it's way over my limitations as this point. This is my formula: =VLOOKUP(I3,Sheet2!A$1:B$350,2). I've tried putting ,False at the end which I hoping would indicate to not include anything or put N/A if it wasn't an exact match in the range. Any ideas? -- Thanks for everyone's help!! "Gayle B" wrote: I'm not getting the desired result using VLOOKUP. I have a multiple page workbook. In the first page (where my formula lives), Column I contains a text string (such as STRQ321). In the second workpage, Column A contains text strings ranging from STRQ1 to STRQ601. Columns B and D contain information I want to have copied into the first workpage. I'm doing a conditional lookup (if the cell in column L says "Y", I do the lookup, otherwise I enter the text "N/A" in the cell). My equation is as follows: =IF((L6="N"), "N/A", VLOOKUP(I6,'STRQ Req Summary'!A:F,2,FALSE)) Excel correctly evaluates I6 as the string "STRQ321". When it goes to the referenced page VLOOKUP comes back with an error (#N/A). If I change FALSE to TRUE, VLOOKUP returns the information from the line with "STRQ320". I temporarily changed the return information from VLOOKUP to be the information contained in Column A of the second worksheet -- this means that I should have the same value from the lookup as is in I6, however I get #N/A when I have FALSE and STRQ320 when I have TRUE. I have changed the type of data in both columns to "Text", "Number" and "General" with no change in the results. I have sorted the data in the second sheet, again with no change in the results. This result happens for every entry within the worksheet as I attempt to do lookups of data. Any ideas? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP Formulas
FYI, "FALSE" as the last argument in a VLOOKUP formula tells Excel that you want only exact matches to the first argument in the formula. In your case what ever is in "I3" need to match exactly something in "A1:A350" in order to return the value one column to the right (indicated by the "2"). Dan Gayle;482790 Wrote: Gayle, I have something similar happening and don't find any spaces. I'm brand new at doing IF and/or Lookup formulas. I'm trying to analyze yours to see if it helps this greenie at all. I think it's way over my limitations as this point. This is my formula: =VLOOKUP(I3,Sheet2!A$1:B$350,2). I've tried putting ,False at the end which I hoping would indicate to not include anything or put N/A if it wasn't an exact match in the range. Any ideas? -- Thanks for everyone's help!! "Gayle B" wrote: I'm not getting the desired result using VLOOKUP. I have a multiple page workbook. In the first page (where my formula lives), Column I contains a text string (such as STRQ321). In the second workpage, Column A contains text strings ranging from STRQ1 to STRQ601. Columns B and D contain information I want to have copied into the first workpage. I'm doing a conditional lookup (if the cell in column L says "Y", I do the lookup, otherwise I enter the text "N/A" in the cell). My equation is as follows: =IF((L6="N"), "N/A", VLOOKUP(I6,'STRQ Req Summary'!A:F,2,FALSE)) Excel correctly evaluates I6 as the string "STRQ321". When it goes to the referenced page VLOOKUP comes back with an error (#N/A). If I change FALSE to TRUE, VLOOKUP returns the information from the line with "STRQ320". I temporarily changed the return information from VLOOKUP to be the information contained in Column A of the second worksheet -- this means that I should have the same value from the lookup as is in I6, however I get #N/A when I have FALSE and STRQ320 when I have TRUE. I have changed the type of data in both columns to "Text", "Number" and "General" with no change in the results. I have sorted the data in the second sheet, again with no change in the results. This result happens for every entry within the worksheet as I attempt to do lookups of data. Any ideas? -- Dan DeHaven ------------------------------------------------------------------------ Dan DeHaven's Profile: http://www.thecodecage.com/forumz/member.php?userid=748 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=133153 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
array vlookup formulas | Excel Worksheet Functions | |||
locking vlookup formulas | Excel Worksheet Functions | |||
Vlookup and Cells with Formulas | Excel Discussion (Misc queries) | |||
VLOOKUP formulas | Excel Worksheet Functions | |||
VLOOKUP FORMULAS | Excel Discussion (Misc queries) |