I'm try to use vlookup formula and doesn't found the value.

The formula is as follow:

=VLOOKUP(CONCATENATE(D3,L3),'[bills up to 022908.xls]Sheet1'!$G$3:$J$50000,4,0)

D3= file number

L3= amount

the index column is another concatenate(file number,amount)

Best regards

Alfredo J

Suggest you do some debugging in steps:

1. Open the 'bills up to 022908.xls'

2. Use an empty cell to "=CONCATENATE(D3,L3)". That way you can see what values are being generated.

3. Copy the values of that cell and see if you can find them in Col G of 'bills up to 022908.xls'.

Once its broken down into pieces you can usually see "the error of your ways."

Hope this helps.

Posted on Mar 10, 2008

Hi,

a 6ya expert can help you resolve that issue over the phone in a minute or two.

best thing about this new service is that you are never placed on hold and get to talk to real repairmen in the US.

the service is completely free and covers almost anything you can think of (from cars to computers, handyman, and even drones).

click here to download the app (for users in the US for now) and get all the help you need.

goodluck!

Posted on Jan 02, 2017

I certainly can't take the time to explain v lookup to you in this forum, but I can direct you to several sites that you can familiarize yourself with it. It's going to take you time to to understand it.

http://spreadsheets.about.com/b/2008/03/17/excel-vlookup-3.htm

http://spreadsheets.about.com/od/excelfunctions/ss/vlookup.htm

http://www.techonthenet.com/excel/formulas/vlookup.php

http://spreadsheets.about.com/b/2008/03/17/excel-vlookup-3.htm

http://spreadsheets.about.com/od/excelfunctions/ss/vlookup.htm

http://www.techonthenet.com/excel/formulas/vlookup.php

Jun 21, 2009 | Microsoft Computers & Internet

This tutorial also helped me much to understand how vlookup works:

http://www.myhowtoos.com/en/excel-howtoos/84-how-to-match-values-in-excel-using-vlookup

http://www.myhowtoos.com/en/excel-howtoos/84-how-to-match-values-in-excel-using-vlookup

Feb 18, 2009 | Microsoft Computers & Internet

http://www.ozgrid.com/Excel/excel-vlookup-formula.htm

This link provides information on usage of vlookup.

This link provides information on usage of vlookup.

Feb 18, 2009 | Microsoft Computers & Internet

=VLOOKUP(A2;Sheet1.$A$3:D27;2;0)

The cell I created this formula in was Sheet 3 Cell C9 - to show the different sheets

A2 is the cell I want to look up

Sheet1.A3:D27 is the range of cells that contains the data I want to return, The first column relates directly to cell C9 is Sheet 3. I locked the first cell in my range as I wanted to apply the same formula across other cells hence the $

2 is the number of the column that has the data I want to return, I had a choice in this formula of 4 columns

0 is the value to complete the formula

The cell I created this formula in was Sheet 3 Cell C9 - to show the different sheets

A2 is the cell I want to look up

Sheet1.A3:D27 is the range of cells that contains the data I want to return, The first column relates directly to cell C9 is Sheet 3. I locked the first cell in my range as I wanted to apply the same formula across other cells hence the $

2 is the number of the column that has the data I want to return, I had a choice in this formula of 4 columns

0 is the value to complete the formula

Feb 11, 2009 | Microsoft Excel for PC

Hi Hss Holdings,

The easiest way to do this is to use a formula called vlookup. You need to have the raw data some where in the workbook, but it can be a separate sheet. Put all the raw data in a table, make sure the account number is on the furthest left column, then start entering the formulas

vlookup(value_to_find, table_to_check, column_index_number, range_lookup(true/false))

value_to_find = the value you want to find on the left most column of the table

table_to_check = the table you want to find the data from

column_index_number = the column number of the data field you want to return into this cell, 1 = the left most column.

range_lookup = false for an exact match, true for the nearest match

Say the table is on sheet2 between A1 and E300

B15 is whatever you type, so no formula needed here

B12 =VLOOKUP(C1,Sheet2!A1:E300,2,FALSE)

G12 = B12 =VLOOKUP(C1,Sheet2!A1:E300,3,FALSE)

etc

The easiest way to do this is to use a formula called vlookup. You need to have the raw data some where in the workbook, but it can be a separate sheet. Put all the raw data in a table, make sure the account number is on the furthest left column, then start entering the formulas

vlookup(value_to_find, table_to_check, column_index_number, range_lookup(true/false))

value_to_find = the value you want to find on the left most column of the table

table_to_check = the table you want to find the data from

column_index_number = the column number of the data field you want to return into this cell, 1 = the left most column.

range_lookup = false for an exact match, true for the nearest match

Say the table is on sheet2 between A1 and E300

B15 is whatever you type, so no formula needed here

B12 =VLOOKUP(C1,Sheet2!A1:E300,2,FALSE)

G12 = B12 =VLOOKUP(C1,Sheet2!A1:E300,3,FALSE)

etc

Jul 12, 2008 | Excel (SS8SATAS5128400R)

Check if the first parameter entered is of the cell for which you are doing lookup.

I mean sometimes its possible if you want to do a lookup for say cell A2, by mistake you enter it as =vlookup(A3,........)

I mean sometimes its possible if you want to do a lookup for say cell A2, by mistake you enter it as =vlookup(A3,........)

Jul 07, 2008 | Microsoft Excel for PC

hi there

this is i found for you ...and i hope this would give you what you are looking for..

http://www.contextures.com/xlFunctions02.html

All the Best.

Was this solution helpful? Show your Appreciation by rating it:

this is i found for you ...and i hope this would give you what you are looking for..

http://www.contextures.com/xlFunctions02.html

All the Best.

Was this solution helpful? Show your Appreciation by rating it:

Feb 20, 2008 | Computers & Internet

If you can move your name column (C) to the first column, you could leverage the VLOOKUP formula pretty easily.

To do this, do the following:

1) Move the C Column to be the A Column, shifting all other columns to the right.

2) (optional) Insert a new row at the top of the sheet (to hold the formula & seach value)

3) Use A1 as your search field.

4) In A2, enter the following formula:

=VLOOKUP($A$1,$A$2:$C$6,3,)

Describing above parameters, in the formula:

$A$1 -> the search field (name your looking for).

$A$2:$C$6 -> The table/grid you wish to search and return values from. The left most column (A) must contain the values to be searched.

3 -> is the column number (A=1,B=2,C=3, etc) within the table/grid to return.

If you cannot make the name column your first (A) column, there are more complex ways to do this. For instance, create a new sheet which redisplays the info in the structure easier for this method, and perform the VLOOKUP on that data. Other options might exist in creating a complex formula that would get you what you want.

Also, if you can sort column A (names) it would find results faster, if your data set is large.

To do this, do the following:

1) Move the C Column to be the A Column, shifting all other columns to the right.

2) (optional) Insert a new row at the top of the sheet (to hold the formula & seach value)

3) Use A1 as your search field.

4) In A2, enter the following formula:

=VLOOKUP($A$1,$A$2:$C$6,3,)

Describing above parameters, in the formula:

$A$1 -> the search field (name your looking for).

$A$2:$C$6 -> The table/grid you wish to search and return values from. The left most column (A) must contain the values to be searched.

3 -> is the column number (A=1,B=2,C=3, etc) within the table/grid to return.

If you cannot make the name column your first (A) column, there are more complex ways to do this. For instance, create a new sheet which redisplays the info in the structure easier for this method, and perform the VLOOKUP on that data. Other options might exist in creating a complex formula that would get you what you want.

Also, if you can sort column A (names) it would find results faster, if your data set is large.

Feb 03, 2008 | Microsoft Excel for PC

VLOOKUP is to Searches for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify in the table. Use VLOOKUP instead of HLOOKUP when your comparison values are located in a column to the left of the data you want to find.
The V in VLOOKUP stands for "Vertical."
Syntax
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.

Aug 30, 2007 | Microsoft Office Standard for PC

Jan 28, 2016 | Microsoft Excel for PC

106 people viewed this question

Usually answered in minutes!

×