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

Free Microsoft Excel 2013 Quick Reference

Embedding functions in Vlookup?

On one tab called CheckSheet I have the following:
Check Range
123 XYZ
456 CDR

On another tab I have ranges named XYZ and CDR. I need to be able to create
a vlookup inside a vlookup. Here is what I am thinking should work but
doesn't:

+vlookup(z1,+vlookup(a1,'CheckSheet'!A1:B3,2),2)

Basically I need a vlookup to use the "range" name that resulted from
another vlookup. I am sure there is something simple I am missing but it is
driving me crazy. Any help is greatly appreciated. The above results in
#value.


Post your answer or comment

comments powered by Disqus
Q. Can we use the INDIRECT function in Vlookup specifically in TABLE ARRAY?

The syntax of VLOOKUP is

[ = VLOOKUP(LOOKUP_VALUE,TABLE ARRAY,COLUMN_NO,FALSE) ]

I want to dynamically provide the "TABLE ARRAY".

I want to have it from a different sheet...

1) Is it possible?

2) Lets say.. I have a sheet called "DATA" from where i will be retreiving this data then normally i wud have the syntax as follows.
TABLE ARRAY - 'DATA'!$B:$Z

No i want to write it this way = INDIRECT("'DATA'!"&LEFT(ADDRESS(1,2,3),2)&":"&LEFT(ADDRESS(1,26,3),2)

3) Using the above with INDIRECT OR Without writing INDIRECT ?

Hi,
Need help in using the Mid function in Vlookup. I want to get the mid of the value which i get using vlookup.

Example: if my Vlookup gives the value as "GSC 03-Parts & Service Systems-GSMS-Test " i want to use the mid function to get the a new value as 03

i'm using the below code but not helping me



Hi,
I have been trying to hardcode a file link in vlookup using concatenate and indirect.

My simple formula looks like this

=VLOOKUP($C12,INDIRECT(CONCATENATE(A13,A14)),2,0) (where the same file different sheet is being referenced)

which works perfectly

but when i give the string

=VLOOKUP(&C12,Indirect(Concatenate("C:[2G Cell Parameters Data1.xls]GCELL'!$B$1:$C$65536")),2,0) (different file and different sheets are being referenced)
the result gives the error #REF!

Kindly advise if I am missing anything
Thanks and regards.

On one tab called CheckSheet I have the following:
Check Range
123 XYZ
456 CDR

On another tab I have ranges named XYZ and CDR. I need to be able to create
a vlookup inside a vlookup. Here is what I am thinking should work but
doesn't:

+vlookup(z1,+vlookup(a1,'CheckSheet'!A1:B3,2),2)

Basically I need a vlookup to use the "range" name that resulted from
another vlookup. I am sure there is something simple I am missing but it is
driving me crazy. Any help is greatly appreciated. The above results in
#value.

In an invoice I am making I want to use the vlookup function to populate a
description column and a unit price column when I type in a product item
number. I have a product table set up with the item number, description and
unit price on it. However, when I do not have an entry in the product item
numberin the invoice, an error message appears in the description column and
unit price column which does not make a good appearance for my invoice.

How to I put the =If(B15="","","") function in the vlookup function
=VLOOKUP(B15,Products,2)

Hi Everyone ,

I have 2 spreadsheets (attached) I need to pull gross salary info from one to another by using EmployeeID. I want to use Vlookup function in VBA However I'm totally VBA Newbie. I have found a code but it does not make sense to me now. I was wondering could you please help me to make necessary changes to get it to work?

Sub TestLookup()
Dim FileName As String
FileName = "C:yelizgross salary.xls"
adoLookup FileName, ActiveSheet.Cells(2, 1)
End Sub

Sub adoLookup(FileName As String, luValue As String)
Dim adoRs As New ADODB.Recordset
Dim adoConn As New ADODB.Connection
Dim query As String
Dim col As Integer

With adoConn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & FileName & ";Extended Properties=Excel 8.0;"
.Open
End With
query = "SELECT * FROM [Sheet1$A1:D6000]"
With adoRs
.CursorLocation = adUseClient
.Open query, adoConn, adOpenStatic, adLockReadOnly, adCmdText
.Fields(0).Properties("Optimize") = True
.Find "EmployeeID = '" & luValue & "'"
If Not .EOF Then
For col = 1 To 3
ActiveSheet.Cells(5, col + 1) = .Fields(col)
Next
Else
MsgBox "Employee " & luValue & " could not be found.", vbOKOnly, "Invalid ID"
End If
End With
End Sub

Regards
Yeliz

Is it possible to Call the inbuilt Excel Functions (like VLOOKUP ) in Macros.
we have to pouplate cells with formula which uses VLOOKUP. one way was to update each cell with required Formula like:

 Range("A1").value = " = 6*F7*(1-Vlookup(lookupvalue........))"
but this was stupid as no of cell to be populated was large and variable and moreover it will return formulae to cells rather
than fixed value

What we tried was to defile an table_array as Array(x y) and then use this array to return required VLOOKUP value.

However I was thinking is there any way to call function like VLOOKUP in Macros so that we can return a value to cell rather than formula.

I have a template MS Word Document that has approximately 65 embedded Excel worksheets. Each worksheet performs a VLOOKUP to evaluate specific conditions based on a risk and threat matrix. Each worksheet is a unique sheet that is relative only to the proceeding paragraph. The last visible field calculates a composite score based on the average of all VLOOKUP results.

I would like to be able to have a final worksheet at the end of the document that calculates the total number of each grading criteria as a summary. I am having great difficulty in referencing values from one embedded worksheet to another.

Specifically – If an embedded worksheet contains a cell value, how is it possible to reference that cell value in another embedded worksheet in the same document?

I had hoped not to have a Word Document and an Excel document, but simply a Word document. This is because this report will go through multiple “Hands” during its critique and revision. I need the functionality of sending only a single “All inclusive document”

If I have chosen an improper method to accomplish this process, please inform me of that as well.

Many thanks,

Jared

Hi Guys,

I've been working on this off-and-on for the last couple of days with no
success. What I have is a worksheet where users enter information that is
then referenced against a norm table. What I'd like to do is allow them to
choose which norm table is used and have the information for that norm table
displayed. The tables are entered in separate worksheets following the first
worksheet.

I have been using a Vlookup function like this:
=VLOOKUP(N16,--------,14,TRUE), where N16 is the value that is looked up
initially, the blank is the Table_Array section, 14 is the column that the
needed value is found, and TRUE will choose the nearest the initial value.
The problem I'm having is that I'd like to insert a function in the array
blank (something I've been told recently is "impossible"...) or have the
array blank use the value of another cell with a generated value that
contains the correct worksheet (norm table) reference. I have tried to use
several different methods, but nothing is working.

=VLOOKUP(N16,T(N43),14,TRUE)
=VLOOKUP(N15,INDIRECT(N43),11,TRUE)

I'm really just an amateur, so I don't have a clue what to do. =) But, I
know you guys will find a solution. Thanks tons!

Josh =)

Using the VLookup Function in Excel 2007

</img></img>
Returns the relative position of an item in an array that matches a specified value in a specified order. Use MATCH instead of one of the LOOKUP functions when you need the position ...

Hi,
I am using VLOOKUP function in sql query. I have two sheets. I am trying to use VLOOKUP function programatically. When I click the button on excel sheet, It loads the data in two sheets. But VLOOKUP is in one of the SHEET'S sql query. But when I run It doesn't display the proper VLOOKUP value. It displays only the formula. I am using VLOOKUP like this.

SELECT roi.ganumber, roi.cont_eff_date,
'=VLOOKUP(A2,''Internal Team Data''!A:D,4,FALSE)'
FROM rpsc_opp_install roi,

It displays only the formula as it is. How can I get the data?

Thank you very much

Hi Guys,

I've been working on this off-and-on for the last couple of days with no
success. What I have is a worksheet where users enter information that is
then referenced against a norm table. What I'd like to do is allow them to
choose which norm table is used and have the information for that norm table
displayed. The tables are entered in separate worksheets following the first
worksheet.

I have been using a Vlookup function like this:
=VLOOKUP(N16,--------,14,TRUE), where N16 is the value that is looked up
initially, the blank is the Table_Array section, 14 is the column that the
needed value is found, and TRUE will choose the nearest the initial value.
The problem I'm having is that I'd like to insert a function in the array
blank (something I've been told recently is "impossible"...) or have the
array blank use the value of another cell with a generated value that
contains the correct worksheet (norm table) reference. I have tried to use
several different methods, but nothing is working.

=VLOOKUP(N16,T(N43),14,TRUE)
=VLOOKUP(N15,INDIRECT(N43),11,TRUE)

I'm really just an amateur, so I don't have a clue what to do. =) But, I
know you guys will find a solution. Thanks tons!

Josh =)

I am trying to use the Vlookup function in a macro but I can't figure out how to write it. I am using a controlled loop to cycle through my data. So for the first entry, my lookup data is in cell A2. The lookup table is located in a sheet called 'Trade Table' and the lookup field is in Column B and value needed is in column C. The results go in column G.

The excel formula is as follows:
I have the macro code started, but can't figure out how to finish it (at least I think it is started correctly)
        For i = 1 To n
            Range("G" & i + 1).Value = Application.WorksheetFunction.VLookup(.Range("A & i + 1"),

        Next i


I have been trying to use the RIGHT function in a VLOOKUP and can not get it to work. For example if my VLOOKUP is

=VLOOKUP(A16,A8:E8,5,FALSE)

In the range I am looking at I only want to look at the right 2 characters in column A.

If cell A8 is M200612 I only want to look for the 12.

Does anyone know how to do this???

Thanks.

Hi,

I need help in executing a VLOOKUP function in my program.

I can get resut using this statement
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'06.30 AM'!C1:C2,2,FALSE)"

How to get the same result using
ActiveCell.Value= Application.Worksheetfunction.VLookup(   )
What to enter with in the bracket to get the same result,as i got using 
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'06.30 AM'!C1:C2,2,FALSE)"

Regards
Abhishek

hi!

I need help in using Now() function in hh:mm format for doing a vlookup.

I am getting the current time using NOW() Function and my previous day's data is referenced with time in hh:mm format (not just in display format, but even created at backend in hh:mm format by actually typing in). But when I do a vlookup, I am not getting the data in the output since excel calculates the time in backend in decimal places, not just in hh:mm format for vlookup.

For example, let us say the current time using Now() function is 09:15 (am) which is something like 41012.3854195602 (for 13 April 2012) for excel's own internal calculation. In another sheet I have entered data for 09:15 (am) which is equivalent to 0.385416666 for excel's calculation purpose.

To match the decimals of Now() data for vlookup with with manual entry time, I took the Now() time and rounded it off to 4 decimal places so that it was matching the decimals of manual entry time. Therefore, 0.38541 of Now() is now equal to 0.38541 of Manual entry.
So when I do a vlookup (and while my data is being updated in my excel sheet in real time for Now() function to work), I will get the vlookup result.
However, sometimes when the rounded off values of Now() are not equal to Rounded off values of manual entry time, there is a mismatch and the data jumps to the last entry for 1-2 seconds, before again reverting back to actual current time.

I was wondering if there is a more elegant solution to match the Now() time in hh:mm with my manually entered time in hh:mm. Please note that I am not talking about display in hh:mm format by changing the time format, but for actual time value in hh:mm format as read by excel.
What I am unable to do is not being able to use is the FALSE condition with my vlookup during comparison of time. Since excel calulates time in decimals and not in human time format, I am currently forced to work with only for approximate matches which is giving me problems.

Following 2 points may please be noted in this regard:
1. Even though I am talking in terms of vlookup about, my data is in array format and I am fetching it using Index and Match functions, which are basically same as vlookup;
2. I would prefer a formula running into multiple columns and rows rather than a Macro since macros generally do not allow reverting of calculations once done. But even than, visitors may advise me on Macros.

Regards,

Naira.

XL97: Range_Lookup Argument in VLOOKUP() Finds Exact Match

In Microsoft Excel versions 5.0 and later, the VLOOKUP() and HLOOKUP() functions contain an argument called range_lookup. The syntax of these functions are different than in ...

Using Lookup Functions in Excel

It is often convenient to have a spreadsheet cell formula use, as part of its computations, a value that it "looks up in a table." The VLOOKUP function in Excel provides ...

XL98: Range_Lookup Argument in VLOOKUP() Finds Exact Match

The syntax of these functions are different than in earlier versions (Microsoft Excel 4.0 ... It is a logical value that specifies whether you want to find an exact ...

How to use the LOOKUP function in Excel

... searches for in the first vector. Lookup_value can be a number, text, a logical value ... The array form of LOOKUP is similar to the HLOOKUP and VLOOKUP functions. The difference is ...

XL97: Range_Lookup Argument in VLOOKUP() Finds Exact Match

The syntax of these functions are different than in earlier versions and are defined as ... It is a logical value that specifies whether you want to find an exact ...

How to use the LOOKUP function in Excel

The array form of LOOKUP is similar to the HLOOKUP and VLOOKUP functions. The difference is that HLOOKUP searches for lookup_value in the first row, VLOOKUP searches in the first ...

XL98: Range_Lookup Argument in VLOOKUP() Finds Exact Match

In Microsoft Excel 98 Macintosh Edition, the VLOOKUP() and HLOOKUP() functions contain an argument called range_lookup. The syntax of these functions are different than in earlier ...

I hope this description of what I am doing makes sense:

I have a sheet containing data of people to whom invitations were sent for an event. The other sheet is where the RSVPs will be recorded. When the last name is typed in, a VLOOKUP formula gets the first name, and then other VLOOKUP formulas fill in all the pertinent data across the row using the last and first name to reference the list on the sheet with the data.
However if my COUNTIF formula shows that there is more than one occurance of the last name, I do not let any first name go in there, and so the data is not filled in for that person. I am trying to make a VBA function where an input box with radio buttons will show the first names, so when that is chosen the rest of the row will fill in too.

The place where I am stuck with this is how to get the corresponding reference to the occurance of the last name. In other words, COUNTIF will tell me HOW MANY occurances there are, but does it reference the specific occurances? I mean, once I have my COUNTIF formula and if the result is 5, for example, is there any way to use the VLOOKUP to match each number to an occurance (first occurance is #1, second occurance is #2, on up to #5) so that I can get my first name off of that occurance of the last name? I need to have some way to somehow use the VLOOKUP function in conjunction with COUNTIF (I think) in order to reference the first names based on those 5 last names. Or is there a better way?


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