Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Vlookup return true false Results

Hi

I have created a couple of fairly simple spreadsheets to help a colleague. All was working fine but no longer.

A monthly Excel file is received showing fee amounts against a salespersons name. No other data is included (as usual, our super systems cannot produce this data...). To aid reconcilliation, I used an existing spreadsheet containing the location and region for each salesperson and created the following:

Spreadsheet A contains the list of salespersons name, location and region. This list is named - MainList.
Spreadsheet B contains the fee amounts and salespersons name.

Using only ss A, the user simply clicks a button and enters the required month, e.g Apr 08

The code then opens ss B, finds the last row with data, then performs the lookup to obtain the location, writes the location to col G, performs another lookup to obtain the region, writes this to col H - that's all it does.
This was working fine until last week and it now does not return any values at all. It simply blanks cols G & H. I had set it up to blank a cell if the lookup could not return a value but not all cells.

I've edited the code to remove some sensitive server data...
Code:
Private Sub CommandButton1_Click()
Dim strMonth
Dim myResult
Dim nxtResult
Dim myPath As String
Dim wbkMonthly As Workbook
Dim x As Long
Dim finalRow As Long

myPath = "serverfolder"

strMonth = Application.InputBox("Enter a Month & Year (use 3 letter format, e.g. 'Oct 07')", "Choose the file", Type:=2)
    If strMonth = False Then Exit Sub
    
Set wbkMonthly = Workbooks.Open(myPath & "Completion and Doc Fees" & " " & strMonth & ".xls")

finalRow = wbkMonthly.ActiveSheet.Range("E65536").End(xlUp).Row

With wbkMonthly.ActiveSheet.Range("G1")
    .Value = "Location"
    .Font.Bold = True
    .Font.Name = "Arial"
    .Font.Size = 10
End With

With wbkMonthly.ActiveSheet.Range("H1")
    .Value = "Region"
    .Font.Bold = True
    .Font.Name = "Arial"
    .Font.Size = 10
End With

For x = 2 To finalRow
On Error Resume Next
    myResult = Application.WorksheetFunction.VLookup(wbkMonthly.ActiveSheet.Range("E" & x), Range("'Completion and Doc Fees
Partner Codes.xls'!MainList"), 2, False)
    If Err  0 Then
        wbkMonthly.ActiveSheet.Range("G" & x).Value = ""
            Else
                wbkMonthly.ActiveSheet.Range("G" & x).Value = myResult
    End If
Next x

For x = 2 To finalRow
On Error Resume Next
    nxtResult = Application.WorksheetFunction.VLookup(wbkMonthly.ActiveSheet.Range("E" & x), Range("'Completion and Doc Fees
Partner Codes.xls'!MainList"), 3, False)
    If Err  0 Then
        wbkMonthly.ActiveSheet.Range("H" & x).Value = ""
            Else
                wbkMonthly.ActiveSheet.Range("H" & x).Value = nxtResult
    End If
Next x

wbkMonthly.ActiveSheet.Columns("F:G").AutoFit
End Sub
Any ideas or hints greatly appreciated.

hello!

i am trying to transfer data from one table to another using vlookup function.

If the check box is ticked, this means that this invoice has been paid and returns a TRUE; otherwise, it remains blank or simply indicates a FALSE.

This is the table I am trying to populate to summarize all outstanding items. Currently I am using a vlookup function to do this. The problem is if an invoice for the quarter has been paid in Table1, i have to manually delete the formula in Table2 so as not to show it as outstanding.

=IF(ISNA(VLOOKUP(A4,Sheet1!A4:Q6,2,0)),0,(VLOOKUP(A4,Sheet1!A4:Q6,2,0)))

I know I can use the TRUE, FALSE and BLANK status in Table1 to make this work more efficient. However, when I tried to do a multiple vlookup function nested in IF, it's not picking any numbers at all.

I thought I'd consult your guidance on this. Any assistance you can extend is truly appreciated.

-annais

I have looked through all the posts with the name Vlookup up, but can't see why my vlookup is failing.

This is the information given by Excel 97 help...

--------------------------------------------
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Lookup_value is the value to be found in the first column of the array. Lookup_value can be a value, a reference, or a text string.

Table_array is the table of information in which data is looked up. Use a reference to a range or a range name, such as Database or List.

· If range_lookup is TRUE, the values in the first column of table_array must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise VLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not need to be sorted.
· You can put the values in ascending order by choosing the Sort command from the Data menu and selecting Ascending.
· The values in the first column of table_array can be text, numbers, or logical values.

· Uppercase and lowercase text are equivalent.

Col_index_num is the column number in table_array from which the matching value must be returned. A col_index_num of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on. If col_index_num is less than 1, VLOOKUP returns the #VALUE! error value; if col_index_num is greater than the number of columns in table_array, VLOOKUP returns the #REF! error value.

Range_lookup is a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, VLOOKUP will find an exact match. If one is not found, the error value #N/A is returned.

Remarks

· If VLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses the largest value that is less than or equal to lookup_value.
· If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error value.
· If VLOOKUP can't find lookup_value, and range_lookup is FALSE, VLOOKUP returns the #N/A value.

--------------------------------------------

I have a lookup table on the second tab of my worksheet, this is not sorted. This area on the second tab is named, and called Gb_Docs

My Vlookup, on the 1st page reads :-

=VLOOKUP(A1,GB_Docs,2,FALSE)

A1 on the first sheet is say AD-001, A2= L2
The GB_Docs data on page 2, looks like this:-

AD-001 Cherries
AK-001 Strawberries
FG-002 Bananas
L2 Cocoa
L8 Junk

The Vlookup, works for the first 3 items in this list, but returns #na for the last 2 items, and yet, according to the excel help, using the FALSE expression on the end, means the list doesn't need to be sorted.

Your starter for 10 is worth 10 points.

I have looked through all the posts with the name Vlookup up, but can't see why my vlookup is failing.

This is the information given by Excel 97 help...

--------------------------------------------
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Lookup_value is the value to be found in the first column of the array. Lookup_value can be a value, a reference, or a text string.

Table_array is the table of information in which data is looked up. Use a reference to a range or a range name, such as Database or List.

· If range_lookup is TRUE, the values in the first column of table_array must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise VLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not need to be sorted.
· You can put the values in ascending order by choosing the Sort command from the Data menu and selecting Ascending.
· The values in the first column of table_array can be text, numbers, or logical values.

· Uppercase and lowercase text are equivalent.

Col_index_num is the column number in table_array from which the matching value must be returned. A col_index_num of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on. If col_index_num is less than 1, VLOOKUP returns the #VALUE! error value; if col_index_num is greater than the number of columns in table_array, VLOOKUP returns the #REF! error value.

Range_lookup is a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, VLOOKUP will find an exact match. If one is not found, the error value #N/A is returned.

Remarks

· If VLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses the largest value that is less than or equal to lookup_value.
· If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error value.
· If VLOOKUP can't find lookup_value, and range_lookup is FALSE, VLOOKUP returns the #N/A value.

--------------------------------------------

I have a lookup table on the second tab of my worksheet, this is not sorted. This area on the second tab is named, and called Gb_Docs

My Vlookup, on the 1st page reads :-

=VLOOKUP(A1,GB_Docs,2,FALSE)

A1 on the first sheet is say AD-001, A2= L2
The GB_Docs data on page 2, looks like this:-

AD-001 Cherries
AK-001 Strawberries
FG-002 Bananas
L2 Cocoa
L8 Junk

The Vlookup, works for the first 3 items in this list, but returns #na for the last 2 items, and yet, according to the excel help, using the FALSE expression on the end, means the list doesn't need to be sorted.

Your starter for 10 is worth 10 points.

I want to search a cell in a report where supervisors report staffing
actions. When overtime is worked they may note it as O/T, OT or ot. I was
wondering if it is possible to put these values in a table then use that
table to search the cell in the form something like

= IF(ISERROR(SEARCH(Vlookup(cell, table, reference),$C3,1)),FALSE,TRUE)

where C3 may contain something like "J Blow 800 - 400/ 400- 600 O/T"

to return true if one of the strings in the table is found or false if it is
not found.

Or is there a better way to do this?

thanks

How can you differentiate between upper case and lower case text in a vlookup
function? For me, vlookup returns the first value when I have te last
variable set to "FALSE". I have one row as "I 18" and the next row as "i
4". When I use vlookup for I, the result is 18. When I use vlooup for i,
the result is 18. If I put "i 4" above "I 18", both results are 4. If I set
the last variable to TRUE, the result is always the value of the lower case
"4" regardless of the row order.

I think certain functions in Excel should be enhanced. Top of mind are
vlookup, hlookup, and getpivotdata. As it is, if I don't want #N/A to
appear, I have to enter my forumla as:
if(isna(formula),0,formula)

This strikes me as ugly and inefficient. Could the formula parameters be
extended to allow the user to define what would be returned on error.
Vlookup would become:
vlookup(value, table, col, true/false, error return value)

I could enter "" (blank), 0, etc as the error return value. If I left it
blank, #N/A would be the default. I suppose the result could even flag the
cell if the error value is returned via the error checking indicator or some
sort of conditional format.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions

Hi:
When I use this formula -

IF(ISNA(VLOOKUP(D9,$A$2:$B$15,2,FALSE)),,(VLOOKUP( D9,$A$2:$B$15,2,FALSE)))
The cell return is 0.

IF(ISNA(VLOOKUP(D9,$A$2:$B$15,2,FALSE)),"
",(VLOOKUP(D9,$A$2:$B$15,2,FALSE)))
The cell return is BLANK.

What I want is for the current cell data to remain, if the
"IF

I am using vlookup to match values on truck numbers on sheet 1 with truck
districts on sheet 2 and excel is returning the #N/A error and I can't find
the problem.
For example
On sheet 1 the truck numbers are in column C formatted as text and the truck
numbers consist of text and numbers (i.e. T1767A42)
On sheet 2 the truck numbers are in column A, sorted ascending, formatted as
text, and are the same numbers as on sheet one. (T1767A42)
Using cell d2 on sheet 1 for my vookup statement I inputted
=Vlookup(C2,sheet2!A2:C547,2,False)
If I leave the range looup as True excel returns the last entry in the
lookup coulmn which is incorrect.
If I change the range lookup to false I get the #N/A error.
Any help would be greatly appreciated.

A1= value to look for in B1:B500
if it finds A1 in B1:B500 I want it to return C1.
I can use a VLookup ok, except when doesn't find A1 it returns a #N/A.
I've tried a IF statement around the VLookup, but still runs into the #N/A
issue.
Any suggestions?

I have two workbooks A, B. I am using vlookup function in workbook A as follows:

Vlookup(A4, [WorkbookB.xls].Sheet!$A$4:$I:$6,3, false).

Through this vlookup function, I am saying the following - if the value in cell A4 is found anywhere between the range of A4-I6 in workbook B then return the content from workbook b, 3rd column.

3rd column contains TEXT. But using the vlookup function, it doens't give me the entire string. Instead it cuts it off in between.

Do you know why this would be?

Thanks much,
Ritu

I think certain functions in Excel should be enhanced. Top of mind are
vlookup, hlookup, and getpivotdata. As it is, if I don't want #N/A to
appear, I have to enter my forumla as:
if(isna(formula),0,formula)

This strikes me as ugly and inefficient. Could the formula parameters be
extended to allow the user to define what would be returned on error.
Vlookup would become:
vlookup(value, table, col, true/false, error return value)

I could enter "" (blank), 0, etc as the error return value. If I left it
blank, #N/A would be the default. I suppose the result could even flag the
cell if the error value is returned via the error checking indicator or some
sort of conditional format.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...heet.functions

I use this formula in Excel 2007 all the time to check whether or not a value in one list is present in another:

if(isna(vlookup(cell,table,column,0)),false,true)

It's kind of a pain to type it in every time so I created a custom function called InList with this code.

 
Function InList(cell, table, column, settype) As Boolean
 
If Not IsError(WorksheetFunction.VLookup(cell, table, column, settype)) Then
InList = True
Else
InList = False
End If
End Function
It works just fine if the item looked up is in table. It returns TRUE and TRUE shows where the formula goes.

BUT if the looked up item is not in table then the value is <out of context> and #value is fed back to the worksheet instead of FALSE. When testing a breakpoint is set at the if not iserror line and then I step thru the code and watch the value InList.

If the lookup is found the routine steps thru to the Inlist=true line and ends function but if lookup is not found the whole if statement is just skipped.

Since InList defaults to False on initiation I tried:

without explicitly setting it to false figuring that the default FALSE would be fed back to the worksheet. NOT happening.

I also tried using the ISNA function to no avail.

What gives. How can I make this work so that when the lookup isn't there FALSE feeds back.

Thanks for any help.

Hi,

Right now I'm using VLOOKUP to return a value, however I need VLOOKUP to return the sum of the values it's looking up IF if finds the value more than once. Example:

=IF(COUNT(VLOOKUP(A1927,range,6,FALSE))>0,VLOOKUP(A1927,range,6,FALSE),0)

this is the formula I use right now. It works, except when the value of cell A1927 appears twice in the "range", it does not return the sum of each instance of the sixth column for each time the value of cell A1927 appears in the "range."

Is there a function (or formula) I can use to sum the values for every time VLOOKUP is true?

Thanks ahead of time,

Rob

I'm comparing one string array with another using VLOOKUP. About half
the list matches exactly if I use FALSE in the Range_Lookup. On the
items that don't match I used TRUE in the Range_Lookup but found it
doesn't get the closest match. See example:

Test Table Array
AAABBBCCC
AAACCCCCC
AAACCCDDD

Lookup Value = AAACCCCC

VLOOKUP returns 1st item in Table (AAABBBCCC) ????????????

Of course item 2 (AAACCCCCC) is the better match but VLOOKUP doesn't
see it that way.

Does anyone know of a way around this problem? Maybe a character by
character match function that returns the item that matchs the most
number of correct characters?

Thanks in advance.

Hi:
When I use this formula -

IF(ISNA(VLOOKUP(D9,$A$2:$B$15,2,FALSE)),,(VLOOKUP(D9,$A$2:$B$15,2,FALSE)))
The cell return is 0.

IF(ISNA(VLOOKUP(D9,$A$2:$B$15,2,FALSE)),"
",(VLOOKUP(D9,$A$2:$B$15,2,FALSE)))
The cell return is BLANK.

What I want is for the current cell data to remain, if the
"IF<ISNA<VLOOKUP" function is TRUE instead of the notorious #NA, Blank,
and "0" returns.

What should I do?
Please help.
Thanks,
Jay

=IF(ISERROR(VLOOKUP(B14,'Metal Worksheet'!$A$8:$L$157,11,FALSE)),"",VLOOKUP(B14,'Metal Worksheet'!$A$8:$L$157,10,FALSE))*D14

As long as the lookup value is true the formula works but if the lookup value is false then I get #value! when it tries to multiply by D14.

How do i get the formula to return a blank cell or 0 value if the lookup value is false?

Thanks.

I have a very straightforward vlookup formula:

=IF(AND(VLOOKUP($AB7,EmpInfo!$A$15:$AD$215,5,FALSE)<18,VLOOKUP($AB7,Summary!$A$10:$I$210,9,FALSE)>40 )=FALSE,"yes","no")

that, when evaluated to the last couple of steps looks like:

if(FALSE=FALSE,"yes","no")
If(TRUE,'yes","no")

why does the result come up a zero?

The cell is not formatted specifically for anything

Thanks in advance

I have a two workbooks of data. The First workbook contains all the products that my company is trying to produce while the other workbook has data from a plant we use that shows what they say we have told them to produce. I've been comparing both spreadsheets in order to see if this plant has the same data as we do at the main office.

So far I have 3 Vlookups that ask:

1) Is the specific job on the Plant workbook on the main workbook?

2) Is the specific job on the Plant workbook on the main workbook, but within the Option A sheet?

3) Is the specific job on the Plant workbook on the main workbook, but within the discontinued sheet?

I have each Vlookup returning a simple "yes" or "no". I wish to find a way to populate another column that will list the reason why the job is discontinued, if of course it is on the discontinued sheet.

So I would only look on the "Is it Discontinued" column for instances of the word "yes". On the Discontinued sheet, there is a column that says a few words on why the job was discontinued. I would like to find a way of creating an IF statement that would basically say:

IF - Discontinued Vlookup column says yes - populate cell with the '[MainWorkbook]DiscontinuedSheet'DiscontinuedReason for that specific job #.

I know this could be done by an IF statement, but I'm not sure how to accomplish this. The Vlookup I've created to give a better perspective on how the spreadsheets are setup is like this:

=IF(ISNA(VLOOKUP(A2,'[MainWorkbook.xls]Discontinued'!$1:$10000,1,FALSE)),"No","Yes")

I know the IF statement would get this done, just not sure how to handle the True statement.

Any help would be awesome!!

I want to search a cell in a report where supervisors report staffing
actions. When overtime is worked they may note it as O/T, OT or ot. I was
wondering if it is possible to put these values in a table then use that
table to search the cell in the form something like

= IF(ISERROR(SEARCH(Vlookup(cell, table, reference),$C3,1)),FALSE,TRUE)

where C3 may contain something like "J Blow 800 - 400/ 400- 600 O/T"

to return true if one of the strings in the table is found or false if it is
not found.

Or is there a better way to do this?

thanks


No luck finding an answer? You could always try Google.