Free Microsoft Excel 2013 Quick Reference

- VBA Vlookup - returns no data whatsoever
- Between vlookup and index
- Vlookup not working properly.
- Vlookup, not working as described.
- Using Vlookup in a string search of a cell
- Vlookup -- Upper case and Lower case text
- Vlookup, add parameter, on error return user defined value
- Leave cell data (IF(ISNA(VLOOKUP) is TRUE instead of returning blank (" ") or #NA
- Vlookup returns incorrect match
- Lookup value - true/false
- Is it true that vlookup returns on 256 char?
- Vlookup, add parameter, on error return user defined value
- Function won't return FALSE
- VLOOKUP for multiple results
- VLOOKUP Closest Match Not Close Enough
- Leave cell data (IF(ISNA(VLOOKUP) is TRUE instead of returning blank (" ") or #NA
- Vlookup returning #value!
- Vlookup Returns Strange Result
- Trying to create IF statement to populate cell with Vlookup Data
- Using Vlookup in a string search of a cell

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 SubAny ideas or hints greatly appreciated.

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

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.

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.

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

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.

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

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

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.

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?

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

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

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 FunctionIt 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.

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

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.

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

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.

=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

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!!

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.