Finding unsorted data

chester12

New Member
Joined
Feb 16, 2014
Messages
43
Office Version
  1. 2010
Platform
  1. Windows
I have 2 sheets with a column of unsorted names.

example

I have the name Final Assault in cell AP1840 of the first sheet. In cell AS1840 I want to print the data for final assault from column F of the second sheet(called sheet2). The title Final Assault occurs in column E of sheet2.

I need to drag the formula down in the first sheet.There are some blank rows in the first sheet.Many of the titles in the first sheet
will not be in sheet 2.

The data in neither sheet can be sorted.

Thanks very much

Chester12
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

chester12

New Member
Joined
Feb 16, 2014
Messages
43
Office Version
  1. 2010
Platform
  1. Windows
thanks

this is not working

in col ap starting at ap1604

Final Assault
Central Flame
Special Catch
Humbie
Indian Temple
Majala
Five In A Row
Nautical Twilight
Blakemount

in sheet 2 in col e
Seeyouatmidnight
Smooth Stepper
Cape Caster
Vendor
Ballyculla
Shotgun Paddy
Wyck Hill
April Dusk
Delusionofgrandeur
Knocklayde Sno Cat
Presenting Rose
Five In A Row
Special Catch
Boudry
K O Kenny


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

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


In col AS of sheet 1 I want to print the values from col F of sheet 2 if the titles in both sheets match. in my example Five In a row and special catch are in sheet 1 and sheet 2. Col F in sheet 2 contains the scores for all titles available.
 

chester12

New Member
Joined
Feb 16, 2014
Messages
43
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

still not working..I have no idea why not
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
still not working..I have no idea why not

But you are not saying what you get: Wrong value, error, nothing?

Note that the formula looks up the value in AP1840 in column E of Sheet2 and, if successful, it returns the corresponding item from column F of Sheet2.
 

chester12

New Member
Joined
Feb 16, 2014
Messages
43
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

But you are not saying what you get: Wrong value, error, nothing?

Note that the formula looks up the value in AP1840 in column E of Sheet2 and, if successful, it returns the corresponding item from column F of Sheet2.

what it is saying using =VLOOKUP(AP1840,Sheet2!E:F,2,0) in cell AS1840 is #N/A in all cells dragged down in AS col.

2 titles are both lists,so it should be returning the numbers from col f for both of these
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
what it is saying using =VLOOKUP(AP1840,Sheet2!E:F,2,0) in cell AS1840 is #N/A in all cells dragged down in AS col.

2 titles are both lists,so it should be returning the numbers from col f for both of these

If you are sure that the value of AP1840 is also in column E of Sheet2, the following should return a >0 count:

=COUNTIFS(Sheet2!E:E,AP1840)

Do we get a >0 count here?
 

chester12

New Member
Joined
Feb 16, 2014
Messages
43
Office Version
  1. 2010
Platform
  1. Windows
it returns zero all the way down col as..but I can clearly see Special Catch on both sheets.I pasted below from each sheet.they
they look identical...however when I copy and paste special catch from sheet 1 to sheet 2 your test works!

there must be some hidden difference?

Special Catch
Special Catch


I am puzzled.


<tbody>
</tbody>

<tbody>
</tbody>
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
it returns zero all the way down col as..but I can clearly see Special Catch on both sheets.I pasted below from each sheet.they
they look identical...however when I copy and paste special catch from sheet 1 to sheet 2 your test works!

there must be some hidden difference?

Special Catch
Special Catch


I am puzzled.

<tbody>
</tbody>

<tbody>
</tbody>

I think you have extraneous spaces around the entries. Either remove them or invoke:

=VLOOKUP("*"&AP1840&"*",Sheet2!E:F,2,0)

Does this fare better?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,561
Messages
5,832,507
Members
430,136
Latest member
Asir Jefferson

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top