Hello:



I need help with a formula. In one sheet i have employee and their specific job code and their country location. On another sheet I have specific job code, country location and pay. How can i bring this over to my first sheet to make it employee specific.

1 sheets columns: Emp IS, Emp Name, JOb Code, Country
2nd Sheet Columns: Country, Job Code and Base Pay

I need my 1st sheet to look like:

Emp Id Emp Name Job Code Country Base Pay
123 XYZ 2415 Argentina 50,000
4812 MNO 2415 Canada 90,000


Any help would be greatly appreciated. Thank you
KDP

You can use VLOOKUP or an INDEX/MATCH using the Job Code ( if it is unique)

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Hi I have almost 50 countries in other sheet... so this would require me to plug in V-lookup those many times. Is there any other way using two criteria. Thank you

Please post a sample sheet with some data and expected results ( click Go advanced - Manage attachments)

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Thank you so much for helping,please see the attached excel sheet

Attached Files

excel Question.xlsx (13.6 KB, 10 views)

Use SUMIFS()

Like

To, me there seems to be more problems here.

In Sheet1!E2 you write:

Need to Bring information here from Sheet 2. if you are in job 2554 and Argentina that bring in Column D values in here

The only Job Code 2554 in Sheet2 is in row 36, country PORTUGAL.

Another problem is that all country names in Sheet2 have a space after the last letter which the country names in Sheet1 do not have. This problem can be solved of course, but I think you should give examples (at least 5) in Sheet1 column E of expected results.

Here is SUMIFS() used on your data.

I defined both dataranges as tables - so all formulas will expand automatically when a new row is added;
I removed spaces from column headers;
On both sheets, I formatted JobCode/Code column as text, as you have some codes as texts;
In country column on 2nd sheet I removed all trailing spaces;
To get at last some test values returned, I changed a couple of job codes on 1st page so, that they have a matching entryes in table on 2nd sheet;
I did rename sheets.

Attached Files

ExcelQuestion.xlsx (15.4 KB, 11 views)

Hi,
yes so two things... as my file was big could not send everything. second, it could be a possibility that if you in specific code and there is no data for that country in second sheet. The data is i am referring her is Base Salary market data and not employee data. I hope this explains. Thank you

@KDP
Please do not quote entire posts unnecessarily. They make the thread hard to read and longer then necessary.
Thx

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

@ArviLaanemets's :I am looking at your excel sheet formula. I am assuming I need to set up criteria for all countries.Right?
@Pecoflyer : Got it... new here so will take care. Thank you

When there is no match, the formula returns an empty cell, i.e. no base pay is registered for this job in this country.

@ArviLaanemets
I am not sure what i am doing incorrectly. but it keeps giving me error. could it be because my actual file sheet 2 has 47 countries and 13,000 rows of data.

What error?

Usually SUMIFS() returns 0 when at least 1 condition is not fulfilled (empty string in my formula, as I included the check for 0's). Possible causes for error I can think of are:
Different lengths of ranges used in SUMIFS() formula (in case you use good old references instead of table syntax);
Wrong names of columns or tables, when you use table syntax.

Trailing space in almost all country names on 2nd sheet would result in empty strings for all such countries returned;

I'm not sure about possibility that job codes are numbers on one sheet, and text on other. In your example file this was not the case - I changed the format only, because some entries were certainly texts.

To convert all job codes to text (do it on both sheets when yo decide for it):
Format the Job Code column as text;
Ino some free column enter the formula
;
Copy the formula up/down, so that every Job Code will be converted;
Copy converted values, and overwrite original job codes with converted ones, using PasteSpecial > Values;
Clear/delete the column where you converted job codes.

For Excel, 1234 and '1234' are different values, and simple formatting is not enough. Until you edit the cell, it preserves old format despite the format you set.

Hi
@ArviLaanemets I am looking in to it now. I greatly appreciate your help. Thank you