Formula needed. Looking a value associated with a common data attribute

dholbs13 · Sep 26, 2019

I have run into a challenge, appreciate any/all help in advance.

I have 2 sets of data, both sets of data have a unique identifier( Employee ID).
ONe set of data has a score( Score 1), and the other Set of data has a score( score 2)

I am trying to perform some form of a value look-up( VLOOKUP won't work) to use the unique identifier in data set 1 t look up that Emp. ID in Data set 2, and return score 2 from that row.

Ultimately, I plan to run a CORREL analysis on multiple data sets( I have that part set-up) but I cannot figure out a INDEX/LOOKUP/MATCH formaula that works.

See example sheet below:

Data Set 1










Data Set 2

<colgroup><col style="mso-width-source:userset;mso-width-alt:2560;width:53pt" width="70"> <col style="mso-width-source:userset;mso-width-alt:2742;width:56pt" width="75"> <col style="mso-width-source:userset;mso-width-alt:2852;width:59pt" width="78"> </colgroup><tbody>
[TD="class: xl67, width: 70"]EMPLOYEE ID
[/TD]
[TD="class: xl68, width: 75"]Score 1[/TD]
[TD="class: xl63, width: 78"]Score 2
[/TD]

[TD="class: xl66"] 101383774
[/TD]
[TD="class: xl64, align: right"]25[/TD]
[TD="class: xl72, align: right"] 85
[/TD]

[TD="class: xl69"]101383775[/TD]
[TD="class: xl70, align: right"]56
[/TD]

[TD="class: xl65"]101383776[/TD]
[TD="class: xl64, align: right"]23[/TD]

[TD="class: xl65"]101383777[/TD]
[TD="class: xl64, align: right"]90[/TD]

[TD="class: xl65"]101383779[/TD]
[TD="class: xl64, align: right"]12[/TD]

[TD="class: xl65"]101383769[/TD]
[TD="class: xl64, align: right"]67[/TD]

[TD="class: xl65"]101383770[/TD]
[TD="class: xl64, align: right"]34[/TD]

[TD="class: xl64"]Emp ID[/TD]
[TD="class: xl64"]Score 2[/TD]

[TD="class: xl65"]101383770[/TD]
[TD="class: xl64, align: right"]55[/TD]

[TD="class: xl65"]101383769[/TD]
[TD="class: xl64, align: right"]60[/TD]

[TD="class: xl65"]101383779[/TD]
[TD="class: xl64, align: right"]65[/TD]

[TD="class: xl65"]101383777[/TD]
[TD="class: xl64, align: right"]70[/TD]

[TD="class: xl65"]101383776[/TD]
[TD="class: xl64, align: right"]75[/TD]

[TD="class: xl65"]101383775[/TD]
[TD="class: xl64, align: right"]80[/TD]

[TD="class: xl66"] 101383774
[/TD]
[TD="class: xl71, align: right"] 85
[/TD]

</tbody>

Fluff · Sep 26, 2019

Hi & welcome to MrExcel.
Why do you say that VLOOKUP won't work? From what you have posted it should work quite happily?

dholbs13 · Sep 26, 2019

Fluff said:
Hi & welcome to MrExcel.
Why do you say that VLOOKUP won't work? From what you have posted it should work quite happily?

I thought the VLOOKUP function can only look up a value from left to right. This means that the column containing the value you look up should always be located to the left of the column containing the return value. My data is coming from different places( sheets) however I do have the luxury of a common identifier( Emp ID)....I tried the combination of INDEX and MATCH functions but I could not get that to pull the record associated with the Emp ID in data set #2

Fluff · Sep 26, 2019

I thought the VLOOKUP function can only look up a value from left to right. This means that the column containing the value you look up should always be located to the left of the column containing the return value.

That is correct, but the data you have posted fits the bill, in that you looking up a value in the 1st & returning the number in the 2nd.
Is what you originally posted an accurate sample of your layout?

dholbs13 · Sep 26, 2019

Fluff said:
That is correct, but the data you have posted fits the bill, in that you looking up a value in the 1st & returning the number in the 2nd.
Is what you originally posted an accurate sample of your layout?

No, it was simplified for the question.

My layout has my common identifier( Emp ID) in Column A on Data set 1, the same common Identifier in column A in a separate tab( however not in the same order as it is from a different data set), and the value I want to return in column N.

Now, I could remove all the data in between Column A and N so they are right netx to each other, however I still have the problem that they are in 2 different tabs. Even if I paste the data from Data set 2 into the rows at the bottom of Data set 1, I still cannot get it to work.

Dan

Fluff · Sep 26, 2019

Ok, 2 options

Book1

A

B

C

D

1

EMPLOYEE ID

Score 1

Score 2

2

101383774

25

85

3

101383775

56

80

4

101383776

23

75

5

101383777

90

70

6

101383779

12

65

7

101383769

67

60

8

101383770

34

55

Sheet1

Cell Formulas
Range	Formula
C2	=VLOOKUP(A2,Sheet2!$A$2:$N$8,14,0)
D2	=INDEX(Sheet2!$N$2:$N$8,MATCH(A2,Sheet2!$A$2:$A$8,0))

Formula needed. Looking a value associated with a common data attribute

dholbs13

New Member

Excel Facts

Fluff

MrExcel MVP, Moderator

dholbs13

New Member

Fluff

MrExcel MVP, Moderator

dholbs13

New Member

Fluff

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

Formula needed. Looking a value associated with a common data attribute

dholbs13

New Member

Excel Facts

Fluff

MrExcel MVP, Moderator

dholbs13

New Member

Fluff

MrExcel MVP, Moderator

dholbs13

New Member

Fluff

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock