Finding unsorted data

chester12

New Member
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 to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Looks like...

=VLOOKUP(AP1840,E:F,2,0)

If not, try to post a small sample.

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.

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

should do what you want...

still not working..I have no idea why not

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.

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

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?

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>

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?

Replies
8
Views
255
Replies
1
Views
93
Replies
6
Views
282
Replies
13
Views
309
Replies
5
Views
361

1,211,680
Messages
6,103,249
Members
447,850
Latest member
thebuzzman15

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.

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