Help with lookup returning non existent values

[Uncle heFTy]

I am trying to produce a frequency pf pick table using a customer's product master file and a sales volume file and have got some peculiar results. Where, for example, a product code appears in the sales volume file but not in the product master file, the lookup returns the description for the next nearest product code above it. I hope that makes sense.

So, is there a way to return, say, 'Not in pmf' instead of the erroneous description or perhaps even " " (as a blank)?

Help greatly appreciated please.

heFFers

[:) Sixthsense :)]

You might have ignored the last argument

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

set it to 0 or false

[Fotis1991]

Perhaps using an IFERROR in front of your formula?

A small sample workbook will helps us to be able to help you.

[Uncle heFTy]

I was just using the lookup not Vlookup, I will try that and report back, thanks - fingers crossed.

[:) Sixthsense :)]

Lookup by default returns the result of approximate match when there is no exact match is found.

[Uncle heFTy]

I tried Vlookup again and couldn't figure out how to get this to work

I am going to attach the sheet (its quite large) and if anyone can help I will be most appreciative - been struggling for hours with this.

Thanks heifers
prod code file.xlsx

[:) Sixthsense :)]

In B2 Cell

Please Login or Register  to view this content.

Drag it down...

If you want to mask the error (i.e. Non Available Items) with a message then use Iferror as suggested by Fotis1991 in Post #3 like this...

In B2 Cell

Please Login or Register  to view this content.

Or if you want to non available items as blank then use like this...

In B2 Cell

Please Login or Register  to view this content.

[Fotis1991]

Perhaps..?

=IFERROR(INDEX($W$2:$W$1621,MATCH(A2,$V$2:$V$1621,0)),"no match")

[Uncle heFTy]

Woohoo, thanks guys, you are wonderful - don't know how to post that its solved

[:) Sixthsense :)]

Glad it helps your and thanks for the feedback.

Please refer my signature area to know how to mark it as solved

[Fotis1991]

.........................