Previous Topic Next Topic

Multible Vlookups

Closed
Stormdronk - Jul 21, 2011 at 06:09 AM
 Stormdronk - Aug 1, 2011 at 05:50 PM
Hello, all

Hope you can help, I have a list of colums.

HR Category Junior Senior
Consultant R 80.00 R 100.00
Analyst R 80.00 R 100.00
Designer R 80.00 R 100.00
Programmer R 80.00 R 120.00
Project Manager R 100.00 R 150.00


I already have a vlookup to give me the result of the HR Category, but now I would like the value to change appropiately when I change it to Junior or Senior.

Col A Col B Col C
Consultant Junior R80
Programmer Senior R120

I want col C/ cell to change when I change B to J/S. The result must be as above Junior consultant gets paid R80

Hope this is clear, thanks for the help!!!!!

2 responses

Answer 1 / 2
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Jul 21, 2011 at 11:19 PM
I was trying to write a formula it gets complicated.
I am giving you a macro
in sheet2 row no. 1 is for header row
so data starts only from row 2
e.g below

heng1 grade data
consultant senior
programmer junior


now try this macro 

Sub test()
Dim r As Range, prog As String, grade As String, findr As Range, findc As Range, c As Range
Dim x As Range
With Worksheets("sheet2")
Set r = Range(.Range("A2"), .Cells(Rows.Count, "A").End(xlUp))
For Each c In r
prog = c.Value
MsgBox prog
grade = c.Offset(0, 1).Value
MsgBox grade
With Worksheets("sheet1")
Set findr = .Cells.Find(what:=prog, lookat:=xlWhole)
Set findc = .Cells.Find(what:=grade, lookat:=xlWhole)
Set x = Application.Intersect(.Rows(findr.Row), .Columns(findc.Column))

MsgBox x
End With
c.Offset(0, 2) = x

Next c
End With
End Sub


BUT BE CAREFUL THE SPELLING OF CONSULTANT, PROGRAMMER ETC AND ALSO JUNIOR AND SENIOR MUST BE EXACT IN BOTH THE SHEETS. If there is mistake there macro will give error.
Answer 2 / 2
Stormdronk
Aug 1, 2011 at 05:50 PM
Sorry for only coming back now, your formula worked grate thanks!!!!!!!!!!!!!