Index Match doesn't seem to return the result i want

**eddyble** · 07-30-2017, 07:14 PM

Capture.PNG Capture1.PNG

Hey Guys,

Hope you are all doing well!

For the life of me i cannot figure out why the index match function isn't working as intended. I have used it many times but it just doesn't seem to spit out the correct result for whatever reason.

For reference top left is A1 and essentially i'm trying to get D10 to return the first day of the month (Australian date format) given it's matching for the month in D9 but it seems to produce completely random results (e.g it only returns July's date after i delete December to June??) which... makes no sense whatsoever.

I'm sure the solution is staring at me in the face but i honestly just cannot see it so hopefully someone can give me a hand with it.

Thanks

Ed

**humdingaling** · 07-30-2017, 07:50 PM

your match seems to be missing the last option

set it to 0

ie

Formula:

Please Login or Register  to view this content.

**eddyble** · 07-30-2017, 07:56 PM

Hi Humdingaling,

Thanks for your response, i always thought it defaulted to 0 if you wrote nothing. I've added it and it doesn't seem to change anything.

Another weird interaction is if i change the match month to August the result is N/A#???

What the heck is going on here

**humdingaling** · 07-30-2017, 08:00 PM

your column A
are those actually dates or months in text form?

if they are dates are they the correct year as your dates in column B?

**humdingaling** · 07-30-2017, 08:01 PM

also

Originally Posted by eddyble

i always thought it defaulted to 0 if you wrote nothing.

match_type Optional. The number -1, 0, or 1. The match_type argument specifies how Excel matches lookup_value with values in lookup_array. The default value for this argument is 1.

it defaults to 1

**eddyble** · 07-30-2017, 08:04 PM

Column A is just text (formatted as text) as is D9 (the match month)

Maybe i should find another way to do this... i've always had problems with dates, so finicky

**humdingaling** · 07-30-2017, 08:15 PM

i'm pretty sure value in D9 is a date and your value in column A is text (judging by the formatting alignment one is left and the other is right)

select everything and format it to general
if it becomes a number you know that cell is a "date" date
if it stays as it is then the cell is text

if this is still not the case attach a sample file so i can see what is happening

**eddyble** · 07-30-2017, 08:33 PM

It didn't change to a number so it indeed is a text string

Here's an exert from the file http://s000.tinyupload.com/?file_id=...18136172020581

Thanks

**humdingaling** · 07-30-2017, 08:50 PM

hmmm, you didnt use the formula i told you to?

change this

Formula:

Please Login or Register  to view this content.

to

Formula:

Please Login or Register  to view this content.

ps also use the site storage to upload
most people will not download from a different site

**eddyble** · 07-30-2017, 08:55 PM

Sorry, i couldn't find an attach button anywhere here.

I did change it to 0 but it didn't change anything so i changed it to a 1 and -1 to see the results (neither of them worked) and i forgot to change it back.

Did that work when it changed to 0? because it doesn't seem to work on mine

**humdingaling** · 07-30-2017, 09:31 PM

does the attached file in post #9 do what you want it to do?

**eddyble** · 07-30-2017, 09:49 PM

Yes! it does exactly what it's meant to, sorry i didn't check it as i assumed you were uploading it to the internal website so that others could download it.

It was a simple solution...

Formula:

Please Login or Register  to view this content.

- was mine

Formula:

Please Login or Register  to view this content.

- is yours.

Thank you very much humdingaling, i feel like a bit of an idiot but that's not too different from the usual

Thanks again

**humdingaling** · 07-30-2017, 10:56 PM

yeah if you put the zero on the index side you will get an error
as you cannot index column 0

you want the 0 in the match side of things so it does the same thing as vlookup

Index Match doesn't seem to return the result i want

LinkBack

Thread Tools

Rate This Thread

Display

Index Match doesn't seem to return the result i want

Re: Index Match doesn't seem to return the result i want

Re: Index Match doesn't seem to return the result i want

Re: Index Match doesn't seem to return the result i want

Re: Index Match doesn't seem to return the result i want

Re: Index Match doesn't seem to return the result i want

Re: Index Match doesn't seem to return the result i want

Re: Index Match doesn't seem to return the result i want

Re: Index Match doesn't seem to return the result i want

Re: Index Match doesn't seem to return the result i want

Re: Index Match doesn't seem to return the result i want

Re: Index Match doesn't seem to return the result i want

Re: Index Match doesn't seem to return the result i want

Thread Information

Users Browsing this Thread

Similar Threads

Index(Match to return 1 row below result

[SOLVED] Index + Match to return result with Left function

Index Match Match Formula DOESN'T WORK WITH TABLES/TABLE NAMES ??

Vlookup/Match/Index combined with IF function to return 2nd to last result

Index & Match - Doesn't return sum of multiple instances

Index and match function using two values to return result

Formula should return a result but doesn't

Bookmarks

Bookmarks

Posting Permissions