# How do I analyse in Excel?

The_Dancer 16:18 07 Feb 2007
Locked

I have a worksheet with three active sheets. I have a small list of data in a column in one sheet, and a huge list of data in a column in another sheet.

I want to find out if any of the data in the small list is currently existent in the large list: e.g. - small list contains unbilled work order numbers, large list contains billed work order numbers, some may be billed that have not left the unbilled column.

Make any sense?

Any ideas?

Been trying formulas for a couple of days now and starting to lose the plot.

VoG II 16:39 07 Feb 2007

Say your small list is Sheet1 and your big list is Sheet2. Order numbers are in column A on both sheets starting at row2.

In Sheet1 B2 (assuming that column B is empty) enter

=VLOOKUP(A2, Sheet2!A2:D100, 2, FALSE)

This will lookup from Column B of Sheet2 and return the corresponding value or #N/A if there is no match.

VoG II 17:38 07 Feb 2007

Forgot to say - copy the formula down as far as needed. Hover the cursor over the bottom right of B2 until the cursor turns into a + then double-click.

Also

=VLOOKUP(\$A2, Sheet2!\$A\$2:\$D\$100, 2, FALSE)

is better.

The_Dancer 08:41 08 Feb 2007

=VLOOKUP(\$A2, Sheet2!\$A\$2:\$A\$7800, 2, FALSE.

I get this result: #REF!

Invalid cell reference error.

VoG II 10:21 08 Feb 2007

The syntax is:

=VLOOKUP(What, Where, TableColumn, True or False)

What = the value to lookup, in this case A1

Where = where to look for the value
in my example this specified a table Sheet2!\$A\$2:\$D\$100 which covers four columns. You have only specified one column Sheet2!\$A\$2:\$A\$7800 so there is nothing to look up. See also next comment.

TableColumn specifies the column number in your data table (Where) to lookup. In my example this was 2 (which says lookup the value in A1 in Sheet1 in the range Sheet2!\$A\$2:\$D\$100 and return the corresponding value from TableColumn 2 - in this case column B. You only specified one column (A) so specifying the 2nd column of your table clearly will not work.

True or False - specifies whether the lookup should return an approximate match (True) or an exact match (False). You need to use False to return an exact match. If you omit the True or False it will default to True - which you do not want.

The_Dancer 10:31 08 Feb 2007

still doesn't seem to work. Do i not need to input an if formula also? just get #n/a now but I know that some of them exist in the large list?

VoG II 10:34 08 Feb 2007

Try creating a simple example like the one I posted click here

and see if you can get that to work. Also see the Help in Excel which gives examples of using VLOOKUP.

VoG II 10:40 08 Feb 2007

Also see click here and turn the sound up to hear the commentary.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Fujitsu Lifebook P727 laptop review

11 best portfolio websites for designers and artists

Office for Mac buying guide: Price, Office 2017 rumours & new features

Comment désactiver les programmes qui s'exécutent au démarrage de Windows 10 ?