LookUp (Excel 2003)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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 , hopefully by end of the year
5) to make this is as one of my hobby …..
6) Are these reasons enough for you to write a marco …….

there are more if you wanna know

Reply | Quote

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!

Reply | Quote

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.

Reply | Quote

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 .

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.

Reply | Quote

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.

Reply | Quote

Ah… Hans,

You are right! and for your earlier reply, I will try to see if I can write a marco on it.

Thank you very much. I appreciate your help.

Reply | Quote