YouTubersLoveExcel#35: IF AND vs. VLOOKUP

Working in Excel 2010:

I have looked at a lot of examples and have probably tried a 100 variations to get this to work, but to no avail.
Not sure what I'm not seeing, it would appear this should be simple. With everything I have tried, I have also
dealt with "sub-script out of range" and "Type mis-match" errors. I have spent days on this little issue. Sucks.

1. UserForm has a ComboBox and Label
2. ComboBox is populated by a Range, from the sheet, in the RowSource property (this works fine)
3. Sheet cells in column "A" are formatted as text, cells in column "B" are formatted as numbers
4. Named range of "Mtl" is defined, workbook wide
5. The Label is to display the vlookup value, this value will also be used in other calculations
6. When the Combobox does a change, the vlookup needs to upgate the Label and variable

?_tried passing different types of variables to vlookup, not sure whats best
?_tried calling the range 2 dozens different ways

Any help would be appreciated.

Thanks.

Hello, I have a slight problem.
I have data in a pivottable which I want to compare with another table.

The lastcolumn+1 of the pivottable needs to get data from another table.

I have this for an example:

Code:

Dim y As Range
Dim lookFor As Range
Dim rng As Range
Dim col As Integer
Dim found As Variant


Set rng1 = Sheets("Invulsheet engineer").Range("A:B")
col = 5
i = 7

For Each lookFor In Sheets("PivotLLI").Range.Column(D, i)
found = Application.VLookup(lookFor, rng1, col, False)
Set Sheet("PivotLLI").Range(mylastcolumn3) = found
i = i + 1
Next

Can anybody give me leads on how to solve this?

Would be appreciated!!


BONUS:
I defined my Lastcolumn succesfully:

Code:

Dim myLastRow2 As Long
Dim myLastColumn2 As Long
Dim mylastcolumn3 As Integer
Dim rng3 As Range

Range("A1").Select
    myLastRow2 = Cells.Find("*", [a1], , , xlByRows, xlPrevious).Row
    myLastColumn2 = Cells.Find("*", [a1], , , xlByColumns, xlPrevious).Column
    mylastcell2 = Cells(myLastRow2, myLastColumn2).Address
    myrange2 = "a1:" & mylastcell2
    mylastcolumn3 = myLastColumn2 + 1
ActiveSheet.Name = "PivotLLI"

What would be faster - doing an exact match VLOOKUP in an entire column, e.g. VLOOKUP(A1, B:C, 2, 0) or creating a dynamic named range in columns B and C with a formula that searches the entire column to find the last used row, e.g.
=OFFSET($B$1, 0, 0, MATCH("*",$B:$B,-1), 2)

and then having each VLOOKUP search in this named range? Is the MATCH in the named range formula calculated each time for each VLOOKUP?

What if I were to have another cell, say D1 and set it equal to
=MATCH("*",$B:$B,-1)

then define the named range as
=OFFSET($B$1, 0, 0, $D$1, 2)

Would that be faster at all? If I am in a sheet set to manual calculation, would that even work since D1 needs to be calculated before the range is set?

Thanks.

I have a standard vlookup formula.

Code:

VLOOKUP(A2,'Prefered Suppliers'!A$2:B$53,2,FALSE)

But the source list will eventually grow. But in the formula, the range is fixed. Is there a way via VBA to monitor the source so that when another row is added (row 54), the formula updates to reflect 54 instead of remaining at 53?

Can you have a vlookup look information up in 2 different sources and only pull information from the source that has data in it (the other one would have empty cells)?

ie MasterSpreadsheetTestTypeA.xls
MasterSpreadsheetTestTypeB.xls

a user will only pick 1 spreadsheet to type text into when the run a test. I'd then like to be able to open a SummarySpreadsheet.xls to collect all the test data into. Basically they would open the SummarySpreadsheet and type in the Unique TestID Name into column A and then columns B thru X would automatically fill in (based on the data that was typed into the appropriate MasterSpreadsheet...).

On a sidenote, after the user fills in all the appropriate data into the MasterSpreadsheet, they'll be doing a "file, save as" and giving it a unique file name to include the TestID name. How can I get the SummarySpreadsheet to take into account this new filename without having to modify every cell's vlookup formula with the unique file name ? Is there a macro that can be run to take whatever text is in column A and add it to the file name in the vlookup formula ?

Hope I'm as clear as mud.
Thanks in advance for your assistance !

I'm writing a function to normalize a date based on a simple table of review dates. If a date is between review date and the valid date, it should be normalized to the review date.

Review Date Valid Until
01-01-1900 14-04-2009
15-04-2009 14-04-2010
15-04-2010 31-12-9999

Following formula in Excel gives correct result '=VLOOKUP(D5,$A$5:$A$7,1,TRUE)' where the D5 contains a date to be normalized and $A$5:$A$7 is the review dates column.

However when I'm trying to replicate this with a following code in VBA the function:


Code:

Function ReviewDate(start_date As Date) As Date
    Dim dteStartDate As Date
    Dim varReviewDate As Variant
    dteStartDate = start_date
    varReviewDate = Sheets("ReviewTable").Range("$A$5:$A$7").Value
    ReviewDate = WorksheetFunction.VLookup(dteStartDate, varReviewDate, 1, True)
End Function

ReviewDate returns different results e.g. ReviewDate("15-04-2009")=15-04-2010.
I suspect some date conversion issue I've already used CDate to conver dates but no success... Attached files contains the code, and a test

Any idea what goes wrong?
Cheers
Artek

It appears I can type a named range in table_array, but I cannot either
1) link to a cell containg the named range. or (#N/A)
2) use a nested Vlookup to return the name of the range.(#VALUE!)

When I nest a VLookup statenent in place of table-array within another
VLookup statement, I receive the error #VALUE!. The value to be returned from
the lookup is the name of the range that the primary Vlookup should use to
find the lookup_value.

I was able to nest a VLookup statmenent for col_index_number within another
VLookup statement successfully.
This is the statement:
=VLOOKUP(D2,(VLOOKUP(B2,capability,2,FALSE)),VLOOKUP(A2,equip,3,FALSE),FALSE)

The second nested Vlookup works, probably because it is returning a numeric
column number value from the "equip" range.
The first nested Vlookup does not work. It looks to the range "capability"
to return the name of the range for table_array.
When placed in its own cell, this formula correctly returns the text name of
the range "speed". When I replace the formula with the test "speed", the
lookup value also works. =VLOOKUP(B2,capability,2,FALSE)
However, if I link the primary lookup to the cell containing this Vlookup,
properly displaying "speed", the primary Vlookup returns the error #N/A.
If I simply type the word "speed" in the cell, and refer to it, this also
does not work and returns the error #N/A.

Hi,

Say you have a database of 20 000 or so entries, and you wish to lookup 1000-3000 values in this database. Would it be faster than the worksheet function VLOOKUP, if you loaded the parts of the database you needed into an array and then used a loop to get the values. something like:
For i = 1 to lastlookupvalue
Lookup(i) = Application.Vlookup(lookupvalue(i), array, x, 0),
Next i
and pasting it back to the worksheet. Do you have experience of which is faster if run from VBA, and the database is separate from the lookup-values.

So I have a spreadsheet (Need Data.xls) that needs to be filled out with a couple columns of data.

This data lays within 338 spreadsheets which have many items and may only have 2, or 3, or 50 that belong on my Need Data.xls spreadsheet.

I have a tab in Need Data.xls named "DIR" which has a list of 336 excel files that need to vlookup'd into.(not a separate file) They're all setup with this format:

Code:

'H:\%location%\[file.xls]Main Template'!$A:$CG

And just to set it up I have this vlookup code:
Code:

=VLOOKUP(B2,(INDIRECT("$O$2")),INDIRECT("O4",TRUE),FALSE)
O2 - Value of "DIR'!$A$1"
O4 - Value of "18"

I did a windows search to find one of the items and changed the value of O2 and I got #N/A as the result.

Please direct me properly so I can get this taken care of.

Thank you in advance for your help.

-Nic