INDEX MATCH from two sheets onto third sheet?

[JuanPrebot0019]

I am trying to trigger a cell to highlight using employee IDs. I want to use the INDEX MATCH function (or anything similar) and I know that LOOKUP functions will not work because the ID #s are not in ascending/descending order and I do not want to sort them. I want to be able to download/copy/paste the updated mass roster on Sheet 1 and download/copy/past the delinquency report on Sheet 2. Sheet 3 would then be used for all the formulas to auto populate all necessary data from both sheets.

The goal is to create a conditional format to change the color for column P2:P500, Sheet 3 (paperwork that has not been submitted) based off blank cells of column AQ2:AQ500, sheet 2 (Delinquency report). To identify the correct employee, I want to use INDEX MATCH using the employee ID #s from column K4:K500, Sheet1 (the mass roster) and cross reference it with the employee ID #s from column A2:A500, Sheet2 (Delinquency report). Is this possible to do with one formula?

 

[etaf]

as you have ID on both sheets and want to look up the ID , then index./match should work
BUT I'm not 100% following what you want

sheet 3 - which column is the Employee ID ?
Lets assume in column A

The goal is to create a conditional format to change the color for column P2:P500,

what sheet is this ???

OK, as i say not following
lets assume you want to copy from sheet1 K4:K500 to sheet3 column B based on ID in column A on all sheets with ID

=index(Sheet1!$K$4:$K$500, Match(A2,sheet1!$A$4:$A$5000,0)) - copy down
so the above will bring back column K from sheet1 where it matches A2 in sheet3 with column A sheet 1
If no match then will return N/A error
you can change that using IFERROR()

But as i say I'm not following what you want to do

If you want to conditional format then it may be possible to use a countif() or countifs() to do that , but not following , so not sure - just thought I would mention that countif() countifs() may be an alternative to index/match if you are not copying and just want to see if that exists and condition
ie
countifs(Sheet2!$AQ$2:$AQ$500,"",Sheet2!$A$2:$A$500, A2)>0

as his is also your first post - then I'm assuming this needs to work in excel 2016 version as in the profile

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone