Vlookup from another Sheet in the same Workbook

[itipu]

Hi All. Once again thanks a lot in advance for your great help.

I attached a sample workbook. I am dumping out of Active Directory (AD) cn - machine name values.

I also have a separate sheet in my workbook where there is a table. Column A - list of machine names.
Column B - list of values corresponding to machine name.
Column C - list of values corresponding to machine name.

I would like to have an If statement like this:

'Below "cn" means machine name, if you need to populate further AD attributes, you put as many as you need using "," note there is no comma after the last attribute:
objCommand.CommandText = "SELECT cn FROM " & "'LDAP://linux.xxx.com/dc=linux,dc=xxx,dc=com' WHERE " & "objectCategory='computer'"
Set objRecordSet = objCommand.Execute

objRecordSet.moveFirst
x = 2
Do Until objRecordSet.EOF
If objRecordSet.Fields("cn").Value = VLookup Then
'Below are attributes that are being populate, remember to add these names to SELECT clause above:
objExcel.Cells(x, 1).Value = objRecordSet.Fields("cn").Value
'objExcel.Cells(x, 2).Value = value corresponding to vlookup 1
'objExcel.Cells(x, 2).Value = value corresponding to vlookup 2
x = x + 1
objRecordSet.MoveNext
Else
objRecordSet.MoveNext
End If


Loop


So that whenever I get a cn (machine name from AD) I check if that name is found in my table (located on a sheet). If not, I skip the record. If found I print cn value in Column A of a new sheet, and corresponding values from my table in Column B and Column C...

Tried playing around with vlookup but did not quite get to work right!

Thanks a lot for your help!

Mike

[Bob Phillips]

I think you want INDEX ... MATCH, although the workbook didn't help me at all.

=INDEX(Sheet2!A:A,MATCH(value,B:B,0))

[itipu]

This one did not work at all...

I tried.. like this:

If objRecordSet.Fields("cn").Value = INDEX(Sheet2!A:A,MATCH(value,B:B,0)) Then

And it does not like A:A ...
I am not too good with Excel specific parts of VB.. so really have no idea about this.

I understand that not everyone has AD to try dumping cn's but I guess at least you can roughly see what goes on... and it is very basic.. I just made an example.

Thanks a lot, Mike

[Norie]

Mike

It's not the A:A it's not liking it's the whole thing.:)

You can't use worksheet functions like that in VBA.

[itipu]

So I guess if I did not make myself clear I need to use VBA to lookup values from Sheet2...

Again, thanks a million

[lucas]

You don't have to use code to use a vlookup on a different sheet....use a named range as the source for the list

[itipu]

Thanks for the tip again I am badly familiar with Excel.
But I googled for Named Range, and I understand that to every cell in my table (which will be fixed) I can assign a name... Or would it be better to assign a name to ColumnA, separate name to ColumnB and ColumnC?

The things is, I need to compare a name from AD to a name in my table, and if it is found in ColumnA of my table, output that machine name, corresponding values from table in a different spreadsheet... (separate spreadsheet is not a problem)... Perhaps a short example? I would really appreciate your help!

Thanks a lot

Mike

[lucas]

I was thinking validation when I mentioned the named range.
the attached uses a formula in the highlighted cells. when you type a coresponding string into the column next to it it fills from matching data on the hiddenDataSheet(it's not hidden in the example but it can be)

[Bob Phillips]

So I guess if I did not make myself clear I need to use VBA to lookup values from Sheet2...

Again, thanks a million

Why?

[itipu]

Given the responce I guess I did not make clear as to what I want.. so as in the workbook I attached I have this code: (Only part of code, the rest is in original attachment)



'So I am dumping cn's here from Active Directory!
objCommand.CommandText = "SELECT cn FROM " & "'LDAP://linux.xxx.com/dc=linux,dc=xxx,dc=com' WHERE " & "objectCategory='computer'"
Set objRecordSet = objCommand.Execute

objRecordSet.moveFirst
x = 2
Do Until objRecordSet.EOF
' Here I want to compare the value of cn to values in Column A of a Sheet2 in my workbook. If it is not found, I will skip to next record.
If objRecordSet.Fields("cn").Value = ????? Then
'Here in the new sheet I will dump cn value in Column A
objExcel.Cells(x, 1).Value = objRecordSet.Fields("cn").Value
'Than I want to get corresponding value from my table's Column B in Sheet2
objExcel.Cells(x, 2).Value = ?????
'Than I want to get corresponding value from my table's Column C in Sheet2
objExcel.Cells(x, 2).Value = ?????
x = x + 1
objRecordSet.MoveNext
Else
objRecordSet.MoveNext
End If


Hope this makes a bit more sence... Please let me know if not ;)

Thanks a lot again!

[itipu]

Lucas provided the solution, not exaclt how I have thought it should be but workable.

So here is his formula:

IF(ISERROR(VLOOKUP(A1,Sheet3!$A:$B,2,FALSE)),"0", VLOOKUP(A1,Sheet3!$A:$B,2,FALSE))

So I want to do something like that:

mike = objRecordSet.RecordCount
ActiveSheet.Range("B1:B$mike").Formula = "IF(ISERROR(VLOOKUP(A1,Sheet3!$A:$B,2,FALSE)),""0"", VLOOKUP(A1,Sheet3!$A:$B,2,FALSE))"

Get a total number of records in my AD array. And select the range from B1 to B(number of records in my array) <--- this does not work.

Also I am not sure as I will populate each cell in this long column will A1 part of the formula increment itself so that if in cell ("B5:B5") it would be A5?

Thanks a lot

Mike

[unmarkedhelicopter]

If objRecordSet.Fields("cn").Value = INDEX(Sheet2!A:A,MATCH(value,B:B,0))

Try a slight modification of Bob's :-
If objRecordSet.Fields("cn").Value = EVALUATE("INDEX(Sheet2!A:A,MATCH(value,B:B,0))")