# Cross reference data from 2 different sheets to fill 3rd sheet

#### arkaran

##### New Member
My sheet 1 (Raw Data) looks like this

 Experience Height P1 Best Tallest P2 Worst Tallest P3 Worst Tall P4 Good Short P5 Bad Tall P6 Worse Short P7 Best Shorter P8 Bad Shortest P9 Worse Shorter P10 Worst Shortest

<colgroup><col><col><col></colgroup><tbody>
</tbody>

My sheet 2 (Rating Scheme) looks like this

 Rating Score 0 1 2 3 4 Experience Best Good Bad Worse Worst Height Tallest Tall Short Shorter Shortest

<colgroup><col><col span="5"></colgroup><tbody>
</tbody>

I want my Sheet 3 (Rating scores) to use the data from the previous two sheets to fill numbers in the following blanks.

 Experience Height P1 P2 P3 P4 P5 P6 P7 P8 P9 P10

<colgroup><col><col><col></colgroup><tbody>
</tbody>

This is obviously a simplified example. I need to use this formula on a huge data set across multiple variables . What formula should I use?

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### j_unsuitable

##### Board Regular
Hey,
can you transposition the data in sheet 2? Make "Height" "Experience" and "Rating Score" column headers and then have the data in the rows? Then you could use a simple vlookup

#### JLGWhiz

##### Well-known Member
Try this
Code:
``````Sub t()
Dim sh1 As Worksheet, sh3 As Worksheet, c As Range, fn As Range, i As Long, txt As String, r As Long
Set sh1 = Sheets(1)
Set sh3 = Sheets(3)
For Each c In sh1.Range("A2", sh1.Cells(Rows.Count, 1).End(xlUp))
For i = 2 To 3
txt = sh1.Cells(c.Row, i).Value
Select Case txt
Case "Best", "Tallest"
r = 0
Case "Good", "Tall"
r = 1
r = 2
Case "Worse", "Shorter"
r = 3
Case "Worst", "Shortest"
r = 5
End Select
Set fn = sh3.Range("A:A").Find(c.Value, , xlValues)
If Not fn Is Nothing Then
If i = 2 Then
fn.Offset(, 1) = r
ElseIf i = 3 Then
fn.Offset(, 2) = r
End If
End If
Next
Next
End Sub``````

#### arkaran

##### New Member
Try this
Code:
``````Sub t()
Dim sh1 As Worksheet, sh3 As Worksheet, c As Range, fn As Range, i As Long, txt As String, r As Long
Set sh1 = Sheets(1)
Set sh3 = Sheets(3)
For Each c In sh1.Range("A2", sh1.Cells(Rows.Count, 1).End(xlUp))
For i = 2 To 3
txt = sh1.Cells(c.Row, i).Value
Select Case txt
Case "Best", "Tallest"
r = 0
Case "Good", "Tall"
r = 1
r = 2
Case "Worse", "Shorter"
r = 3
Case "Worst", "Shortest"
r = 5
End Select
Set fn = sh3.Range("A:A").Find(c.Value, , xlValues)
If Not fn Is Nothing Then
If i = 2 Then
fn.Offset(, 1) = r
ElseIf i = 3 Then
fn.Offset(, 2) = r
End If
End If
Next
Next
End Sub``````

This doesn't use the data from sheet 2. I can't manually write a code for the actual data, as the sheet 2 in the actual data is huge.

#### arkaran

##### New Member
Hey,
can you transposition the data in sheet 2? Make "Height" "Experience" and "Rating Score" column headers and then have the data in the rows? Then you could use a simple vlookup

I tried that, but it's a case of nested vlookup and it's not working.

Replies
2
Views
121
Replies
0
Views
294
Replies
1
Views
103
Replies
3
Views
332
Replies
0
Views
166

1,129,467
Messages
5,636,456
Members
416,919
Latest member
twc2c

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back