Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    LookUp (Excel 2003)

    Hi all,

    I want to do a look up on the values :

    1) in column B and column E based on the values in column A and column D.

    For example, if I enter a value in col A such as the id in the attached, then the name will appear in col B and I enter
    the secid in col C, then the holding will appear in col D.

    2) look up the values in col B and col D based on the value in col C. For example, the value of Secid is enter and the
    result would be show in col B and col D.

    What is the formula to use for this. Can I use Vlookup in this?
    Attached a dummy sample.
    TIA
    Attached Files Attached Files
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: LookUp (Excel 2003)

    Here is a workbook with a solution for #1. It uses a combination of VLOOKUP, INDEX and MATCH. Note that the formulas in D1317 are array formulas.
    Attached Files Attached Files

  3. #3
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: LookUp (Excel 2003)

    Hi Hans,

    Thank you for the solution. Is it impossible to perform a lookup for the case 2?
    How can I tell when to use the combination of Vlookup together with Index and Match

    How do I modify the formula into macro codes?

    Thank you for your guidance.
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: LookUp (Excel 2003)

    VLookup can be used to search for a value in the first column of a range, and return a value from a column to the right of it.
    VLookup cannot be used to return a value in a column to the left of the search column, or to search for a value in a combination of columns. The combination of Match and Index is more flexible, it lets you perform all kinds of lookups.

    What would you want to do in a macro?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: LookUp (Excel 2003)

    Here is a macro solution for #2:
    - Right-click the sheet tab of Sheet1
    - Select View Code from the popup menu.
    - Paste the following code into the module window:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Long
    Dim s As Long
    If Not Intersect(Target, Range("G13")) Is Nothing Then
    Application.EnableEvents = False
    ' Clear F and H
    Range("F12:F17,H12:H17").ClearContents
    ' Initialize
    s = 12
    ' Loop through SecIDs in column D
    For r = 2 To Range("D1").End(xlDown).Row
    If Range("D" & r) = Range("G13") Then
    ' Next row
    s = s + 1
    ' Copy values
    Range("F" & s) = Range("B" & r)
    Range("H" & s) = Range("E" & r)
    End If
    Next r
    Application.EnableEvents = True
    End If
    End Sub

    - Switch back to Excel.
    - Test by entering a value in G13.

  6. #6
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: LookUp (Excel 2003)

    Hi Hans,

    Thank you for your effort. I would like the marco for #1 to perform the same result as in the formula you have
    given and for it to pop up a msg if the data enter is non existent.

    Thank in advance.
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: LookUp (Excel 2003)

    Why would you want a macro? In general, if a problem can be solved using a formula, that is much more efficient than using VBA code.

  8. #8
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: LookUp (Excel 2003)

    Hi Hans,

    Totally agree with you on the formula's efficiency. I want a marco because of the following :
    1) to add this to my codes' library
    2) to learn and study how I can modify for other type of scenarios
    3) I am planning a small program for which entirely base on users interactive with Data form, ie worksheet. hide
    4) to be able to assist all expert volunteers in the Lounge later on <img src=/S/bow.gif border=0 alt=bow width=15 height=15> , hopefully by end of the year
    5) to make this is as one of my hobby ..... <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    6) Are these reasons enough for you to write a marco ....... <img src=/S/please.gif border=0 alt=please width=31 height=23>

    there are more if you wanna know <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: LookUp (Excel 2003)

    If it's (partially) a learning excercise, I suggest that you look at the code I provided for problem #2 and adapt it for problem #1. Good luck!

  10. #10
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: LookUp (Excel 2003)

    Hi Hans,

    I paste the codes into the sheet1's module and do a test, its work for once and after I change the number of Name holding
    a same value in the data set, it is not working anymore <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45> .

    Also if I want to include the whole of Column G except G1, do I change

    If Not Intersect(Target, Range("G13")) Is Nothing Then to If Not Intersect(Target, Range("G")) Is Nothing Then
    and
    If Range("D" & r) = Range("G13") Then to If Range("D" & r) = Range("G") Then

    Attached the sample with the codes.
    Attached Files Attached Files
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: LookUp (Excel 2003)

    The code only runs if you change cell G13.
    There's no point in extending G13 to all of column G, for it makes no sense entering a company name in - for example - cell G1 or G14.

  12. #12
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: LookUp (Excel 2003)

    Ah... Hans,

    You are right! <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30> and for your earlier reply, I will try to see if I can write a marco on it. <img src=/S/read.gif border=0 alt=read width=19 height=33>

    Thank you very much. I appreciate your help. <img src=/S/salute.gif border=0 alt=salute width=15 height=20>
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •