Pivot table or function?

[jod227]

Hello all,

I have data in 2 separate workbooks. In the first one I have data that includes a column that contains textual data that matches with (in different order) a column in the other workbook.

I have a number score in a column that's next to the text column in the 1st workbook (ex. 1-100) and a text score next to the text column in the 2nd workbook (ex. Yes, no, maybe). I ultimately need to see the scores next to each other based on the matching text in workbook 1.

Would this require an advanced pivot table of some sort? Any insight on this would be greatly appreciated!


Workbook 1 **what outcome would be**

white	50.8	yes
blue	6.2	no
green	70.3	yes
red	9

<tbody>
</tbody>

Workbook 2

yellow	no
green.0
green	yes
red
white	yes
blue	no
blue.0	maybe

<tbody>
</tbody>

(Workbook 2 has a few thousand more rows than 1 but all of workbook 1's textual data is contained within the larger column in Workbook 2 - I only care about the text in Workbook 1)

 

[oldbrewer]

Workbook 1 **what outcome would be**
white	50.8	yes	<<<<<<
blue	6.2	no
green	70.3	yes
red	9	0
Workbook 2
yellow	no
green.0
green	yes
red
white	yes
blue	no
blue.0	maybe
	formula in cell marked <<<<<
	=IF(ISERROR(MATCH(A3,$A$10:$A$16,0)),"",OFFSET($A$9,MATCH(A3,$A$10:$A$16,0),1))

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

 

[jod227]

Brilliant! This is exactly what I need, thanks oldbrewer!

 

[oldbrewer]

your homework is to get rid of the zero against red in top table..........

 

[jod227]

I basically just highlighted the column and did a find and replace but I didn't figure out how to do it in the formula.

 

[oldbrewer]

=IF(ISERROR(MATCH(A3,$A$10:$A$16,0)),"",OFFSET($A$9,MATCH(A3,$A$10:$A$16,0),1)) becomes =IF(ISERROR(MATCH(A3,$A$10:$A$16,0)),"",if(OFFSET($A$9,MATCH(A3,$A$10:$A$16,0),1)="","",OFFSET($A$9,MATCH(A3,$A$10:$A$16,0),1)) may not have right number of brackets as I have not checked it <tbody> </tbody>

<tbody>
</tbody>