#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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?
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 149
Default VLOOKUP Formulas

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?



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Art Art is offline
external usenet poster
 
Posts: 587
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
array vlookup formulas michelle Excel Worksheet Functions 0 February 22nd 07 06:03 PM
locking vlookup formulas Jane Excel Worksheet Functions 7 February 6th 07 12:57 AM
Vlookup and Cells with Formulas nejohnso76 Excel Discussion (Misc queries) 2 August 9th 06 06:36 PM
VLOOKUP formulas Donna Excel Worksheet Functions 4 July 9th 06 12:08 AM
VLOOKUP FORMULAS PB Excel Discussion (Misc queries) 1 November 24th 05 11:43 PM


All times are GMT +1. The time now is 01:01 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"