vlookup function incorrect,

**sambucaman** · 02-10-2012, 06:20 AM

Hi guys,

Hoping to get some help with a reverse calculating VAT invoice I am working on.

I have all my calculations correct, and my invoice does exactly what I want it to.... however....

I have a list of about 100 products, all with different prices. This list can be on the same worksheet, or another, hidden away somewhere, and consists of two columns; a description, and a corresponding price. The list is maybe 100 rows.

I would like to be able to pick from a drop down list in Cell C2, and have C2 to display the product, and Cell D2 to display the price.

I have attached what I have so far, and would really appreciate some help. I know there is software out there for invoicing, but I'd rather try and get this fixed if poss.

Really appreciate any help, and I hope you chaps can understand what I'm on about!

Thanks, Andy

**Kyle123** · 02-10-2012, 06:23 AM

You're almost there you need to look at the Vlookup function.

In D2

PHP Code:


=VLOOKUP(C2,K:L,2,FALSE)

**sambucaman** · 02-10-2012, 07:01 AM

Many thanks for swift answer Kyle123 - however that is total gobbledegook to me! Any chance you (or anyone else) could expand on your response? I shall visit Mr Google to search for VLOOKUp in the meantime.

**TMShucks** · 02-10-2012, 07:18 AM

=VLOOKUP($C2,$K:$L,2,FALSE)

The first parameter, $C$2 is what you are searching for; in the first case: 6" x 5" Print
The second parameter is the range ($K:$L) you want to search; the first column in the range is the one being searched
The third parameter, 2, is the column within the range from which the matching entry is returned
The last parameter, FALSE, says look for an exact match. As you are using a drop down DV list this should always be found

Regards, TMS

**sambucaman** · 02-10-2012, 10:40 AM

thanks for all your input - i now have things working exactly as I want them, apart from the speed - I have used =IF(LEN(C12)=0,"",VLOOKUP(C12,'Print Price'!$A$1:$B$44,2,FALSE)), and ever since using the =IF(LEN(C12)=0 part the whole spreadsheet has gone verrrrry slow. Any ideas?
Reattaching incase anyone needs to look at it.

**Kyle123** · 02-10-2012, 11:00 AM

It didn't seem particularly slow to me, but I have amended your named ranges so they don't have to evaluate the whole column.

See if this makes a difference

**TMShucks** · 02-10-2012, 01:04 PM

I prefer INDEX to OFFSET for named ranges but, again, it didn't seem to be much quicker.

OFFSET is volatile whereas INDEX is not, hence the choice.

Desc: ='Print Price'!$A$1:INDEX('Print Price'!$A:$A,COUNTA('Print Price'!$A:$A))
Description: ='Print Price'!$A$1:INDEX('Print Price'!$B:$B,COUNTA('Print Price'!$A:$A))
Postage: =Postage!$A$1:INDEX(Postage!$B:$B,COUNTA(Postage!$A:$A))

I'd also choose to turn the comparison around in the formulae.

For example: =IF(LEN(C12)=0,"",VLOOKUP(C12,'Print Price'!$A$1:$B$44,2,FALSE))
Becomes: =IF(LEN(C12),VLOOKUP(C12,'Print Price'!$A$1:$B$44,2,FALSE),"")

Regards, TMS