Lookup, Index or Match...

[Simon_Excel]

I have two sets of data I need to check each against. I have 1360 Job Titles which need cross referencing against 310 Assignments to see if the Assignment Pay Rate is equal to or higher than the Rate Card Minimum for that Job Title.

The tables below shows the two different data sets which needs checking and returning. As you will see, the Job Titles have additional information in brackets, which are not included in the Assignment Job Title (perhaps another hurdle to come across when returning/looking up information).

Data set 1: This is the data that I will need to check against. There are 1360 lines of data.

Job Title	Rate Card Minimum
6 Sigma Black Belt - 1-23-SS10 (1724312)	21.08
Account Coordinator-20-R430 (1724280)	19.56

<tbody>
</tbody>

Data set 2: This is the data I need to return the Assignment ID's if the Assignment Pay Rate is less than the Rate Card Minimum from the table above.

Assignment ID	Assignment Job Title	Assignment Pay Rate
5613333	Accounting Analysis Clerk-17-008A	9.57
3868610	Accounts Payable Clerk I-16-230A	7.38

<tbody>
</tbody>

I am looking for a way to return the Assignment ID's for where the Assignment Pay Rate is less than the Rate Card Minimum.

I am fairly new to Excel and have tried to look at vlookup, index and match, although I have confused myself and believe a formula may be the best approach, but not sure.

I thank you in advance for looking at this and hopefully providing me with a solution.

If you need any additional information to assist with this, please let me know and I will gladly provide.

 

[gaz_chops]

Try this, adjust the ranges to suit

=IFERROR(IF(C11 < <index<font color="#ab30d6">($B$2:$B1500,MATCH(B11&"*",$A$2:$A$1500,0)),A11,""),"")</index

[TABLE="width: 579"]
<tbody>[TR]
[TD]Job Title[/TD]
[TD]Rate Card Minimum[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6 Sigma Black Belt - 1-23-SS10 (1724312)[/TD]
[TD="align: right"]21.08[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Accounting Analysis Clerk-17-008A (1724280)[/TD]
[TD="align: right"]19.56[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Assignment ID[/TD]
[TD]Assignment Job Title[/TD]
[TD]Assignment Pay Rate[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5613333[/TD]
[TD]Accounting Analysis Clerk-17-008A[/TD]
[TD="align: right"]9.57[/TD]
[TD="align: right"]5613333[/TD]
[/TR]
[TR]
[TD="align: right"]3868610[/TD]
[TD]Accounts Payable Clerk I-16-230A[/TD]
[TD="align: right"]7.38[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Simon_Excel]

Hi Gaz, thank you for sending that over, although I am confused as to what the different elements of the formula are referring to. I wonder if you could simplify the formula by showing what $B$2:$B$1500 etc are.

Try this, adjust the ranges to suit

=IFERROR(IF(C11 < <index<font color="#ab30d6">($B$2:$B1500,MATCH(B11&"*",$A$2:$A$1500,0)),A11,""),"")

[TABLE="width: 579"]
<tbody>[TR]
[TD]Job Title[/TD]
[TD]Rate Card Minimum[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6 Sigma Black Belt - 1-23-SS10 (1724312)[/TD]
[TD="align: right"]21.08[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Accounting Analysis Clerk-17-008A (1724280)[/TD]
[TD="align: right"]19.56[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Assignment ID[/TD]
[TD]Assignment Job Title[/TD]
[TD]Assignment Pay Rate[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5613333[/TD]
[TD]Accounting Analysis Clerk-17-008A[/TD]
[TD="align: right"]9.57[/TD]
[TD="align: right"]5613333[/TD]
[/TR]
[TR]
[TD="align: right"]3868610[/TD]
[TD]Accounts Payable Clerk I-16-230A[/TD]
[TD="align: right"]7.38[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

</index

 

[gaz_chops]

Hi Simon,

C11 = Assignment Pay Rate
B2:B1500 = Rate Card Minimum
B11 = Assignment Job Title (the &"*" after it is a wildcard, to allow for the number in brackets)
A2:A1500 = Job Title
A11 = Assignment ID

So
If C11 is less than Rate Card Minimum it returns the ID, otherwise a blank.

HTH

Gaz

 

[Simon_Excel]

Thanks Gaz, that has helped perfectly! I have no returns and they are all blank, so I hope that means I have no assignments below the minimum pay rate!

Hi Simon,

C11 = Assignment Pay Rate
B2:B1500 = Rate Card Minimum
B11 = Assignment Job Title (the &"*" after it is a wildcard, to allow for the number in brackets)
A2:A1500 = Job Title
A11 = Assignment ID

So
If C11 is less than Rate Card Minimum it returns the ID, otherwise a blank.

HTH

Gaz

 

[gaz_chops]

You're welcome, I would test by changing one or two to be less, make sure they return the expected result. Then change back