LOOKUP function help

[jakeruby]

hi all, first post here...

so I have two worksheets, one with ~1000 entries (names in col. A with other data filled out) and the other, my lookup table, with the same names (except some are missing, ie only ~800 names in this sheet) in col. A with the relevant data i am using the lookup function for.

while i have successfully formulated the function as it correctly returns values, it only returns values for the first ~800 names on the ~1000 entry worksheet with no regard for the name im looking up.

for example, if names 1,2,3 are on worksheet A, but only names 1,3 are on my lookup table, it returns value for 2 next to 3.

if this seems confusing please let me know, the issue is that i have a different number of entries for my lookup table then the worksheet i am using the lookup function for.

thanks

PS-- i should also note that since there are less values in my lookup table then my function table, after the first ~800 entries (the amount that returns all data from the lookup table) the function simply returns 0 for the rest of the ~200 entries even if they are in the lookup table and I am searching for their values

[Pepe Le Mokko]

Could you please post a small sample sheet?

[jakeruby]

here is a sample
SAMPLE.xlsx

in this case, the function would return fda and yt values for as and fda respectively with 0s for the last 2 entries

=LOOKUP(A2;report!A2:A789;lookup!B2:B748) is the code from my sheet

[jakeruby]

here is a sample
SAMPLE.xlsx

in this case, the function would return fda and yt values for as and fda respectively with 0s for the last 2 entries

=LOOKUP(A2;report!A2:A789;lookup!B2:B748) is the code from my sheet

[TMS]

And it might be helpful to tell us what the formula is.

Regards, TMS

[vlady]

can you re-attach the sample file..

[jakeruby]

SAMPLE.xlsx

thanks again for the help

[TMS]

Try:

=LOOKUP(A2;lookup!$A$2:$A$1000;lookup!$B$2:$B$1000)


Regards, TMS

[TMS]

Actually, that won't work because your example doesn't have a sheet called "lookup"

[TMS]

Thanks for the rep. Is this solved?

[TMS]

Please answer in the thread, not by PM.

thanks for your help..

the problem is that the formula you sent is essentially the same thing as my original. my formula is correct but im wondering if there is a way to nest another forumal inside the same cell?

do you know if there is a way for the formula to check if the name (A) is in the lookup table, and if not to skip that entry? that is the issue since there are more entires in my first table then in my lookup table

jake

The formula is not essentially the same as yours. The number of entries in your first table is irrelevant but the lookup references should both be on the same sheet and have the same number of entries. The lookup will return #N/A if the value is not found. This can be trapped using IFERROR and a default value provided.

I think you should have a look at the help for these functions.

Regards, TMS

[jakeruby]

that is the issue, they do not have the same number of entries which is why there is a discrepancy...

can i turn this into another question and ask if there is an easy way to delete entries not present on both sheets? vlookup has not worked but if you can find a way with the sample i provided above it would be appreciated

[TMS]

With your example data, to return the "Line of Service":

=IFERROR(VLOOKUP(Sheet1!$A2,Sheet2!$A:$D,2,FALSE),"Not Found")

Filter on Not Found to list those that do not appear on the reference sheet.

If you wish, you can select the visible rows and delete them.

Regards, TMS