Finding unsorted data

chester12

New Member
Joined
Feb 16, 2014
Messages
38
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
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

chester12

New Member
Joined
Feb 16, 2014
Messages
38
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
38

ADVERTISEMENT

still not working..I have no idea why not
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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
38

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,201
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
38
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,201
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,057
Messages
5,599,550
Members
414,316
Latest member
ExcelLee

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