# Complex Excel Vlookup or Other Solutions Welcome

#### hscott82

##### New Member
Hello, I am trying to create a solution to point out employees on a spreadsheet that need to be researched for hours processing. When they have over 40 hours they go on a spreadsheet we have to look at their worked location and hours columns.

I was thinking either a Vlookup or maybe even a Conditional Formatting that will highlight the Differences.

First, I need to look at their Base Rate (Column J) on Sheet 1, compare to their REG1 (out of other codes) Column N on Sheet 2 to see if they have a higher Rate paid in Column P; their could be multiple values.

Next I need to compare their Home Location (Column E) on Sheet 1 to their worked location on Sheet 2 (Column U).

I have images if it helps!

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

#### alansidman

##### Well-known Member
Post some samples for a clearer understanding.

#### hscott82

##### New Member
We need to compare the Work Site Name (U) to the Home Loc Name (J) and compare the Base Rate (H) to the Rate (P) and highlight if there is a difference on the Research page. We spend a LOT of time manually going through 100+ lines of employees to see if there is a difference and there may be 20-30/100 that need a change noted on the Research tab in Columns J and K.

#### DanteAmor

##### Well-known Member
You can clarify the following:
- I take the "099-houston office" data from column U of the "Origin" sheet
- I look for the data in the "search" sheet in column J, but there is this data: "099-CREW"
- So what I should look for is the data "099", is it always the first 3 digit?
- I compare the data "9.5" of column H of the "origin" sheet against the data of column "I" of the "search" sheet, but there I find this "\$ 9.50", it seems that on one sheet you have text and in the other a number, is that so?
- Do something with column J of the "origin" sheet against column D of the "search" sheet?
- Which sheet should be marked?
- In column K of the "search" sheet should something be put?

Finally, can this be done with a macro?

#### hscott82

##### New Member
Hello Dante,

-Yes it is always the first 3 digits.
-Yes I believe the 9.50 on each sheet is formatted differently, we could always change the format if needed.
-No we don't need to compare J or K from Research, those are added manually by hand AFTER our researching is done. Mostly just looking at everything to the left of J/K.
-Research sheet should highlight when there is a difference that needs to be researched.
-Again, J/K are just manual columns. Nothing needs to be auto-populated there.

#### DanteAmor

##### Well-known Member
Try this formula to put a result in column L

Book1
IJKL
1BASEWORKRESULT
29.5099-CREWSame
35.55470-MANAGERDoes not exist in Orig
47.2022-CREWDifference
Research
Cell Formulas
RangeFormula
L2:L4L2=IFERROR(IF(I2=INDEX(Orig!\$H\$2:\$H\$20,MATCH(LEFT(J2,3)&"*",Orig!\$U\$2:\$U\$20,0)),"Same","Difference"),"Does not exist in Orig")

#### hscott82

##### New Member
It is having some problems, I am wondering if its because the Orig sheet where it looks at is a huge file of 8,000+ employees so it has to look through all of those. It may need to look at the SW# (the employee #) to reference and find the employee on the Orig sheet?

This is what the whole top of that Research sheet looks like, there are more rows on the right and above.

Thank you so much for all your help, I can see where this is going and its so amazing!

#### hscott82

##### New Member
This is how it looks before any work is done, J/K are blank.

#### DanteAmor

##### Well-known Member
So I did not understand what to look for and where to look.

"We need to compare the Work Site Name (U) to the Home Loc Name (J) "

But if column J is empty, then how will it be compared?
I think you should start over with the explanation.

The images are small and I can not see the data well I suggest you use the following to put the data you have on both sheets.
You can hide the columns without relevance to properly show the examples.

XL2BB - Excel Range to BBCode

#### hscott82

##### New Member
Ok, lets try this. Sorry if I made it more complicated than it needed to be. I just need to look up the Employee (by #) from the "Research" tab and compare Home (D) and Base (I) to the "Orig" tab Work Site Name (U)(3 digits) and Rate (P) to see if there is a difference between either?

I attached the images so they open up bigger, hopefully

#### Attachments

• 15.9 KB Views: 2
• 20.8 KB Views: 2
• 38.5 KB Views: 2

1,102,867
Messages
5,489,367
Members
407,686
Latest member
Chuck1960

### This Week's Hot Topics

• Timer in VBA - Stop, Start, Pause and Reset
[CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
• how to updates multiple rows in muliselect listbox
Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
• Delete Row from Table
I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
• Assigning to a variable
I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
• Way to verify information
Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
• Active Cell Address – Inactive Sheet
How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...