General VLOOKUP setup on my datasheet enquiry

b10 =

Please Login or Register  to view this content.

what relationship does the value in B10 have to the weights? A slight difference on what oeldere gave you is =vlookup(b8,c6:e6,3,False) But it will not work unless you change the values each time in cells C6 and E6. You might need to be more clear about what you want if these answers don't get you what you want.

EDIT: I see you posted while I was typing. Still not clear though, if you want for example to have a set of values beside col K that defines what weights they are in col L then this formula would work. =VLOOKUP(B8,K3:L28,2,false)

In that case you don't add the value 70 in it.

=Vlookup(B8,$C$6:$E$7,3,0)

Lookup the value in B8 (in your case 70)

In the range C6:E7 (you only have 1 row with values in your example)

Take the 3 rth column (column E)

Use only an exact match (1 is the other option).



If you want to have the value of sugar the formula would be:

=Vlookup(B8,$C$6:$E$7,2,0)


Hope I explained well enough, if not please come back again.

Hi,

Yes sorry I didnt explain clearly. It is just the weight (g) ONLY that I need VLOOKUP to work on. So user selects any CC value, it then auto calculates the weight (g) proportionately.

So just to double check now, which formula I need to use now?

this would be my interpretation of what you want, still unclear though but you can see the process.

In your updated file, you get #N/b since 20 is not in your list.

Change the value 20 in 70 and see that the result changes to 31.

The problem is that what you are showing in the weight cell is only based on one CC value so unless you show how the weights relate to the other CCs it cannot be calculated and a formula will be difficult for us without guessing. I tried to add a value in cell F6 that showed the weight as a proportion to the CC then used it in the CCs in col K and calculated the weights in col L and then gave you a formula for that. Give it a look and you can see how a vlookup will work.

Glad that worked for you. Don't forget to mark your post as solved and you can thank all those who helped with their time and effort by adding to their reputations, that is always welcome whether successful or not.