VLOOKUP and INDEX MATCH not working all the time / Intermittant results

[MrImpeccable]

So I have a range of data, some are high values that are good and some are low so I am using VLOOKUP / INDEX MATCH to score the values against a scoring system but they only work half the time.

Also It seems that Excel has trouble recognising <=30 or >=30 type operators?

I wondered if anyone could help? Many Thanks!

[AliGW]

Welcome to the forum!

Both functions work perfectly IF they are set up correctly. You will need to share a sample workbook with us.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[MrImpeccable]

Okay many thanks! I have attached the spreadsheet, I have a scoring system on the same page also in the tab labelled Data. You will see the first tab is the results and the second tab under gradings, I am using formulas (VLOOKUP and INDEX MATCH) to use those scoring systems to fetch a value between 0-10 only. This is so I can then make radar charts and add up scores for my test samples A-G.

Have a look, see what you think?

Many Thanks

[AliGW]

Where am I looking for the formulae??? Please give the tab name and cell references.

[AliGW]

OK - found them.

When you are using either VLOOKUP or INDEX MATCH with an approximate lookup (i.e. with 1 or TRUE in the formula), you need to have the lookup data arranged in ASCENDING order. Yours is in DESCENDING order, which is why it's not working properly.

[romperstomper]

One immediate observation, if you use 1 as the match type argument with MATCH, the table must be sorted in ascending order, not descending. You'd use -1 for a table in descending order.

[MrImpeccable]

Really? Is that it? Thank you so much, I will try this. Such a small thing haha! I will try this!

[AliGW]

Yes - that's it.

Let us know how you get on.

[MrImpeccable]

One immediate observation, if you use 1 as the match type argument with MATCH, the table must be sorted in ascending order, not descending. You'd use -1 for a table in descending order.

Ahh okay, thank you.

[AliGW]

What Rory has said is true, but won't work with VLOOKUP, so you'd have to convert all formulae to INDEX MATCH.

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

[MrImpeccable]

Im just going to make sure they are all in ascending order, I will report back to see if it works. Many Thanks! Ive been doing this for days trying to solve this haha!

[AliGW]

Should have joined sooner!!!

By the way, please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.

[MrImpeccable]

So with my scoring system, one will always be ascending and the other will always be descending, or at least sometimes. Which needs to be ascending? The grading system that I need to get a value back from?

[romperstomper]

The lookup table needs to be sorted appropriately (on the first column for a VLOOKUP or the MATCH column, as applicable).

[AliGW]

I have been informed that you have cross-posted this same request here: https://www.mrexcel.com/forum/excel-...t-results.html

Please read our forum rules before opening another thread here: cross-posting is not forbidden, but if you do it, you MUST let us know when you open the thread (the same rule applies at Mr Excel). Thank you.

[MrImpeccable]

Sorry guys just marked this as unsolved as I still need a bit more clarification, thanks for all your help.

So 1. transfer all VLOOKUP functions to Index Match...

2. Transfer the scoring data to ascending - this is the bit I am unsure about as many data will be ascending whilst the scoring system will be descending, is it only the scoring system that needs to be ascending or will putting -1 on Index Match solve this and mitigate it?

3. Is that all that is needed? Thanks!

Thanks!

[AliGW]

Please confirm that you have read and understood post #15 before we proceed.

[romperstomper]

If your lookup table is sorted descending, then use -1.

[MrImpeccable]

I cross posted as I was unsure if I would get replies / how long it would take and was unfamiliar with the forums. I probably wont cross post again as the other forum does not allow me to post attachments. Thanks.

[MrImpeccable]

Please confirm that you have read and understood post #15 before we proceed.

Yes understood.

[AliGW]

Deleted - misleading information - sorry!

[MrImpeccable]

So index column is ascending (grade array), Match column (lookup_array) is descending , using a value of -1 gives me N/A and using a value of 1 gives me zero when the answer should be 7. Thanks for your help by the way.

[romperstomper]

Is your lookup value greater than the first value in the Match column?

[AliGW]

Actually, I've been misleading you - sorry.

You MUST have the lookup array in ascending order.

Excel 2016 (Windows) 32 bit

	B	C	D	E	F
2	Score	Grade		Lookup Value	Grade
3	7.5	B		0	U
4				1	D
5				2	D
6				4	C
7				6	B
8				8	A
9				10	A*

Sheet: Sheet1

Excel 2016 (Windows) 32 bit

	C
3	=INDEX($F$3:$F$9,MATCH(B3,$E$3:$E$9,1))

Sheet: Sheet1

The -1 is to do with the way the lookup is done, not the order of the lookup array.

[MrImpeccable]

Okay thank you, I will have a look and sort this out. Many Thanks AliGW.

[AliGW]

From the MS Office support page:

1 or omitted - MATCH finds the largest value that is less than or equal to lookup_value. The values in the lookup_array argument must be placed in ascending order, for example: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.
0 - MATCH finds the first value that is exactly equal to lookup_value. The values in the lookup_array argument can be in any order.
-1 - MATCH finds the smallest value that is greater than or equal tolookup_value. The values in the lookup_array argument must be placed in descending order, for example: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.

To be honest the -1 argument has never worked as it should when I've tried to use it, so I would advise always arranging your lookup values in ascending order.

[romperstomper]

That's not correct. If you use -1 with MATCH the lookup table must be sorted in descending order.

[AliGW]

Please mark the thread as solved.

[AliGW]

That's not correct. If you use -1 with MATCH the lookup table must be sorted in descending order.

Yes, but it doesn't work as the OP wants or needs. That's the point.

[romperstomper]

I was taking issue with your comment "The -1 is to do with the way the lookup is done, not the order of the lookup array."

[AliGW]

Yes, I know.