Vlookup/match?

[Chris]

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

 

[Max]

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]

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








- Show quoted text -

It gave me a NA

 

[Max]

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
---

 

[Chris]

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.

 

[Max]

Welcome
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500, Files:358, Subscribers:55
xdemechanik

 

[Chris]

Welcome
--
Max
Singaporehttp://savefile.com/projects/236895
Downloads:17,500, Files:358, Subscribers:55
xdemechanik

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?

 

[Max]

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$100)*(E2=Sheet2!D$2$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
---
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?

 

[Chris]

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$100)*(E2=Sheet2!D$2$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
---
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

 

[Max]

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