Need help on VLookup to find result based on desired number

**tuongtu3** · 01-14-2013, 03:19 AM

Hello All,
I'm having a hard time getting a correct VLookup formula to find the result based on desired number. Please take a look at the sample in the attached file and help me out.

Regards,
tt3

**benishiryo** · 01-14-2013, 03:42 AM

hi tt3. your table is pretty difficult to do a lookup. i did a static one you may have to manually adjust if your columns or requirements change.
=INDEX(Data!$B$2:$K$8,MATCH(D3,Data!$A$2:$A$8,0),LOOKUP(Y3,{1;25;50;100;250},{2,4,6,8,10}))

basically the red portion uses Qty in Y to lookup an exact match in these values {1;25;50;100;250}. if it can't find an exact match, it goes to the last biggest value. so for eg. Qty of 150 is no found, so the next biggest value is 100. it will then give me the result vector, which i input as {2,4,6,8,10}. hence, i'll get 8. that is actually the column number i want for my INDEX formula

**tuongtu3** · 01-14-2013, 01:38 PM

Hi benishiryo,
1. The correct number at Part7 should be 1 but formula gave number 0.
2. These values {1;25;50;100;250} in Sheets("Data") will not be the same. It could be any different numbers. When I changed the values (values {1;25;50;100;250}) then the result is wrong.
Can you take a look and see if you can help. Thank you very much for your time and help.

Regards,
tt3

**tuongtu3** · 01-15-2013, 01:46 PM

hello All,
Any alternative method/formula to get a right reuslt would be great.

Regards,
tt3

**benishiryo** · 01-15-2013, 10:16 PM

try this array formula:

Please Login or Register  to view this content.

you have to paste the formula inside the formula bar & press CTRL + SHIFT + ENTER

**tuongtu3** · 01-16-2013, 08:54 PM

Hi benishiryo,
The code is perfect as I expect and I'm still thinking how to apply it to my ws.
Thnak you very much for your time and help.

regards,
tt3