Vlookup/match?
 

Can anyone help me out with creating a formula that does the
following.

sheet1 has a 'JobCode' in column C
sheet 2 has a 'JobCode' in column A with an associating 'Level' in
column E.

I need to match the JobCode from sheet1 and sheet2 and pull the
associating 'Level' in column E on sheet2 back to sheet1 in column G.

Any help is greatly appreciated.

Thanks,

Chris

Vlookup/match?
 

Assume data in row2 down
In Sheet1,
In G2: =INDEX(Sheet2!E:E,MATCH(C2,Sheet2!A:A,0))
Copy down
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500, Files:358, Subscribers:55
xdemechanik
---
"Chris" wrote in message
...
Can anyone help me out with creating a formula that does the
following.

sheet1 has a 'JobCode' in column C
sheet 2 has a 'JobCode' in column A with an associating 'Level' in
column E.

I need to match the JobCode from sheet1 and sheet2 and pull the
associating 'Level' in column E on sheet2 back to sheet1 in column G.

Any help is greatly appreciated.

Thanks,

Chris

Vlookup/match?
 

On Aug 29, 12:46*am, "Max" wrote:
Assume data in row2 down
In Sheet1,
In G2: =INDEX(Sheet2!E:E,MATCH(C2,Sheet2!A:A,0))
Copy down
--
Max
Singaporehttp://savefile.com/projects/236895
Downloads:17,500, Files:358, Subscribers:55
xdemechanik
---"Chris" wrote in message

...



Can anyone help me out with creating a formula that does the
following.

sheet1 has a 'JobCode' in column C
sheet 2 has a 'JobCode' in column A with an associating 'Level' in
column E.

I need to match the JobCode from sheet1 and sheet2 and pull the
associating 'Level' in column E on sheet2 back to sheet1 in column G.

Any help is greatly appreciated.

Thanks,

Chris- Hide quoted text -

- Show quoted text -

It gave me a NA

Vlookup/match?
 

"Chris" wrote
It gave me a NA

Then I suspect that your JobCode data is inconsistent

Here's some options to the earlier to improve robustness in matching

If its text, try in G2:
=INDEX(Sheet2!E:E,MATCH(TRIM(C2),Sheet2!A:A,0))
TRIM removes extraneous spaces

If its numbers, try one of these options in G2:
=INDEX(Sheet2!E:E,MATCH(C2+0,Sheet2!A:A,0))
+0 converts text numbers to real numbers

=INDEX(Sheet2!E:E,MATCH(C2&"",Sheet2!A:A,0))
&"" converts real numbers to text numbers

=INDEX(Sheet2!E:E,MATCH(Text(C2,"000000"),Sheet2!A :A,0))
TEXT converts real numbers to text numbers, padding leading zeros to 6
digits (adapt to suit)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500, Files:358, Subscribers:55
xdemechanik
---

Vlookup/match?
 

On Aug 29, 1:22*am, "Max" wrote:
"Chris" wrote

It gave me a NA

Then I suspect that your JobCode data is inconsistent

Here's some options to the earlier to improve robustness in matching

If its text, try in G2:
=INDEX(Sheet2!E:E,MATCH(TRIM(C2),Sheet2!A:A,0))
TRIM removes extraneous spaces

If its numbers, try one of these options in G2:
=INDEX(Sheet2!E:E,MATCH(C2+0,Sheet2!A:A,0))
+0 converts text numbers to real numbers

=INDEX(Sheet2!E:E,MATCH(C2&"",Sheet2!A:A,0))
&"" converts real numbers to text numbers

=INDEX(Sheet2!E:E,MATCH(Text(C2,"000000"),Sheet2!A :A,0))
TEXT converts real numbers to text numbers, padding leading zeros to 6
digits (adapt to suit)
--
Max
Singaporehttp://savefile.com/projects/236895
Downloads:17,500, Files:358, Subscribers:55
xdemechanik
--- *

Yes, you are right. I got it to work after a while, thanks for the
extra tips and for your help.

Vlookup/match?
 

Welcome
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500, Files:358, Subscribers:55
xdemechanik
---
"Chris" wrote
Yes, you are right. I got it to work after a while, thanks for the
extra tips and for your help.

Vlookup/match?
 

On Aug 29, 1:36*am, "Max" wrote:
Welcome
--
Max
Singaporehttp://savefile.com/projects/236895
Downloads:17,500, Files:358, Subscribers:55
xdemechanik
---
"Chris" wrote
Yes, you are right. I got it to work after a while, thanks for the
extra tips and for your help.

What would I need to change in the formula if I wanted to match 2
columns instead of one. For example,

sheet1 has a 'JobCode' in column C and 'DepartmentID' in column E
sheet 2 has a 'JobCode' in column A and 'DepartmentID' in column D
with an associating 'Level' in
column E.


What if I wanted to match column C and column E in sheet one to column
A and column D in sheet2 before returning the associating 'Level' in
column E on sheet2?

Vlookup/match?
 

Chris

Pl post as new threads for new queries in future

In Sheet1,

Put this into G2's formula bar, then array-enter, ie press CTRL+SHIFT+ENTER
to confirm the formula (instead of just pressing ENTER):

=INDEX(Sheet2!E$2:E$100,MATCH(1,(C2=Sheet2!A$2:A$1 00)*(E2=Sheet2!D$2:D$100),0))

Copy G2 down. Adapt the ranges to suit. All ranges must be identically
sized, and entire col references cannot be used.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik
---
"Chris" wrote
What would I need to change in the formula if I wanted to match 2
columns instead of one. For example,

sheet1 has a 'JobCode' in column C and 'DepartmentID' in column E
sheet 2 has a 'JobCode' in column A and 'DepartmentID' in column D
with an associating 'Level' in column E

What if I wanted to match column C and column E in sheet one to column
A and column D in sheet2 before returning the associating 'Level' in
column E on sheet2?

Vlookup/match?
 

On Aug 29, 8:52*pm, "Max" wrote:
Chris

Pl post as new threads for new queries in future

In Sheet1,

Put this into G2's formula bar, then array-enter, ie press CTRL+SHIFT+ENTER
to confirm the formula (instead of just pressing ENTER):

=INDEX(Sheet2!E$2:E$100,MATCH(1,(C2=Sheet2!A$2:A$1 00)*(E2=Sheet2!D$2:D$100)*,0))

Copy G2 down. Adapt the ranges to suit. All ranges must be identically
sized, and entire col references cannot be used.
--
Max
Singaporehttp://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik
---
"Chris" wrote
What would I need to change in the formula if I wanted to match 2
columns instead of one. For example,

sheet1 has a 'JobCode' in column C and 'DepartmentID' in column E
sheet 2 has a 'JobCode' in column A and 'DepartmentID' in column D
with an associating 'Level' in column E

What if I wanted to match column C and column E in sheet one to column
A and column D in sheet2 before returning the associating 'Level' in
column E on sheet2?

thanks - works great

Vlookup/match?
 

That's good.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik
---
"Chris" wrote
thanks - works great