PDA

View Full Version : [SOLVED] seek help for VLOOKUP



apple
10-29-2007, 07:37 AM
Can anyone show me as file that i attach.

Please do see my explanation as below:

1. Sheet 1 name "find" tab extract the hobby as refer sheet 2 tab name "data1".
a) When click "search" button, the result will pull in column C tab "find"

2. Sheet 2 & 3 will show the differentiate result show in sheet 4 when click button vlookup data 1 and data 2

Thank you very much

Aussiebear
10-30-2007, 08:10 AM
G'day Apple, Firstly, are you sure a simple vlookup formula won't work here? In cell C9 of the Find sheet enter the following formula: =Vlookup(B9,Data1!$A$5:$B$8,1,False) and fill down.
As to your other request, I take it that you are wanting any non matching data between the lists on data1 and data2 to be placed on the vlookup sheet. My suggestion is that you try to use a filter, but I have no experience in this. Others here may be able to help you.

apple
10-30-2007, 08:27 AM
Hi,

1.My request is to lookup search hobby in tab "find" and lookup the data hobby in sheet tab "data1". If data availabe in tab data1, then the hobby
in tab "find" column C will fill up after click search button.

2. I want searching the different between data tab 1 and data tab 2 hobby and result different exist in sheet tab vlookup

Thanks

Aussiebear
10-30-2007, 08:32 AM
Does not the suggestion of a vlookup formula give you a solution to the first part of your issues?

apple
10-30-2007, 08:36 AM
Hi,

Yes, my question part 1 not a vlookup. It's a search the data in other sheet. It's means that whatever data is in tab data1 it will exist in column C tab "find" after press search button. Data in other sheet is just like database. If database found the data it will fill up the column C hobby. Can u help me on this?

Aussiebear
10-30-2007, 08:42 AM
Did you enter the formula I provided in C9?

apple
10-30-2007, 08:51 AM
Hi,

No, i dint put your formula that you given. How to set ur formula in coding? If my user click search button, it will direct show up without put any formula.

figment
10-30-2007, 11:23 AM
your first option can be done withought a macro but if you must have one this will work


Sub Macro1()
For a = 9 To 20
If Range("B" & a) <> "" Then
Range("C" & a).FormulaR1C1 = "=VLOOKUP(RC[-1],data1!R[-4]C[-2]:R[-1]C[-1],2,FALSE)"
Else
Range("C" & a) = ""
End If
Next
End Sub