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

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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>
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

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