Vlookup Not working Properly

[josepfh]

Hello everyone,

I am trying to get values as per their property (see image).

As you can see, there is 65 written in column D with property Excellent next to it, then on next cell I have put a formula that should search from table (on right side) and show Demand type (20, 30, 40...), in this case 20 because 65 with excellent property lies under 20 demand. Similarly, if the number comes under Very Good column then it should show respective Demand number.

I have tried searching over the internet but nothing is working. I know my formula is wrong. I wonder if you experts can help me up?

Moreover, please also tell how to put range of specific numbers in one cell, for example, 01-65, 66-75 and so.

P.S. I am not excel expert.

Many thanks for your support.


CJ5PE.png

[Winon]

Hello josepfh,

Welcome to the Forum.

We cannot see exactly what your Image contains.

Please attach a sample workbook instead.

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Regards.

[AliGW]

Try this (untested):

=INDEX($J$27:$J$31,SUMPRODUCT(($K$26:$O$26=E28)*($K$27:$O$31=D28)*(row($J$27:$J$31)-26)))

It would be easier to help if you provided the workbook instead of an image of it as we could test our propositions first.

[josepfh]

Many thanks guys for your input.

I have attached the file. See if you can fix it.

[josepfh]

Many thanks for bringing this issue to my attention, AliGW. Here are the links:

http://www.msofficeforums.com/excel/...tml#post113963
https://www.excelforum.com/excel-gen...-properly.html
https://www.mrexcel.com/forum/newrep...eply&p=4806003

And this forum, that's it. Hope it helps.

Actually, this is a problem given by my tutor to solve and I need to submit report tomorrow. That's why I posted on multiple forums to get quick response. My bad!

Are we good?

[AliGW]

Yes. See my first suggestion above - I have not had time to try it in your file yet.

That's a lot of cross posts - I hope you have provided links on those other forums, too.

[josepfh]

Yes. See my first suggestion above - I have not had time to try it in your file yet.

That's a lot of cross posts - I hope you have provided links on those other forums, too.

lol. Alright my apologies, boss. Thanks and I am trying the formula you posted above and I don't see it working. :/

[josepfh]

Also, if you could tell me how to put a range of numbers like 01-65 in one cell, please do so.

[AliGW]

OK - your workbook is quite different to the image originally posted. Having multiple numbers (e.g. 0-65) is not Excel-friendly, so I am going to ignore that requirement unless you can give me a really compelling (and I mean REALLY compelling!) reason why you must have them. Going to have another look now.

[josepfh]

OK - your workbook is quite different to the image originally posted. Having multiple numbers (e.g. 0-65) is not Excel-friendly, so I am going to ignore that requirement unless you can give me a really compelling (and I mean REALLY compelling!) reason why you must have them. Going to have another look now.

Many thanks AliGW.

To desensitize the data, I did remove a few things. On the contrary, the range is important because those random numbers lie between these ranges that's why I need range however I found somewhere that if we put least numbers like 65 then 75 so excel takes that as range. I don't know if that's right.

[AliGW]

You don't need your numbers like that to make them work - you'll see below:

Excel 2016 (Windows) 32 bit

	B	C	D	E	F	G
2	Demand	Excellent	Very Good	Good	Average	Worst
3	20	0	0	0	0	0
4	30	66	56	46	36	66
5	40	76	81	87	66	-
6	50	81	91	-	-	-
7	60	90	-	-	-	-
8
9
10
11	1	18	Excellent	98	Excellent	60
12	2	9	Excellent	27	Excellent	20
13	3	45	Very Good	51	Very Good	20
14	4	24	Excellent	87	Excellent	50
15	5	17	Excellent	15	Excellent	20
16	6	38	Very Good	87	Very Good	40
17	7	36	Very Good	50	Very Good	20
18	8	13	Excellent	26	Excellent	20
19	9	42	Very Good	16	Very Good	20
20	10	9	Excellent	2	Excellent	20

Sheet: Sheet1

Excel 2016 (Windows) 32 bit

	G
11	=INDEX($B$3:$B$7,MATCH(E11,OFFSET($B$3:$B$7,,MATCH(F11,$C$2:$G$2,0)),1))

Sheet: Sheet1

HOWEVER, you need to get rid of the leading spaces that were in the headings of the top table or this won't work.

[josepfh]

Alright. I am waiting. And I have done as you suggested for range of numbers and headings.

[AliGW]

Sorry for the delay. I've added the solution to post #11 above.

[josepfh]

Sorry for the delay. I've added the solution to post #11 above.

Oh my god, you're angel! That's wonderful. I don't words to explain my feelings right now. Thank you sooo much Ali!

[AliGW]

You are most welcome! I enjoyed working it out for you.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[josepfh]

One more thing I would like to request from you that is how to delete my posts from other two forums?

[AliGW]

You'll have to contact an administrator on each of them.