Multible Vlookups

[Closed]
Report
-
 Stormdronk -
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 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
801
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.
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Sorry for only coming back now, your formula worked grate thanks!!!!!!!!!!!!!