INDEX MATCH function returns result #N/A

I am new to this forum and also new to INDEX/Match so please bear with me. I have two workbboks RW and Red. In RW the columns ISI and BUI acquire their values from another workbook I now want to calculate a value for HFI by looking up ISI and BUI in Red. Red has two sheets C3 and M2. First need a working formula. In my example I get #N/A. Is it my syntax or perhaps the wrong function?

Second (and maybe this should be a follow up post?) how do I use the listed fuel type in RW to specify which sheet to read in Red? I am using Office2003. Below is a SnagIt image of my files.. Previewing this post I don't see my files so may need some help just running the forum..

Red.xlsRW.xls

When you write a match function like this

=MATCH(B2,A2:A22,) <--Notice no entry after the last comma.
This assumes the 3rd argument to be a 0
=MATCH(B2,A2:A22,0)
This makes the match look for an EXACT match.
Which of coarse there is no exact match in the table.
You're looking up 4.9 in a table of whole numbers 1,2,3,4,5,6,7,8,etc..

Try changing that to
=MATCH(B2,A2:A22,1)
or
=MATCH(B2,A2:A22) <-- Notice no comma at all, this makes it assume 1 instead of 0.

However, this searches for the largest value that is LESS than the lookup value.
So it's going to return 4, but it looks like you want it to return 5

Can you sort Column A DEscending (22, 21, 20 etc..) ?
Then use -1 as the 3rd argument
=MATCH(B2,A2:A22,-1)


Hope that helps.

Thanks. I'll try your suggestions. Also may have to ROUND the ISI value to nearest whole number?

Originally Posted by morrisondan

Also may have to ROUND the ISI value to nearest whole number?

That would work to, use Roundup or ceiling

=MATCH(CEILING(B2,1),A2:A22,0)

Tried reverse order sort with type -1 as well as removed comma and still get #N/A.

To follow up.. Also changed the ISI value to 5.0 and still does not work. Doesn't work but do not see a formula error. Now attempting do a trace.

Can you attach an actual copy of the book, not just a picture?

And copy the sheet from the 2nd book into the first, and just reference that for now.
So we only need to see 1 book.

YOu need to apply the ceiling on BOTH match values.
Just like column B on the Day1 sheet, column C also contains decimal values.
But the formatting of the cell made it DISPLAY rounded to a whole number, But the real value is still there.

In E2, put
=INDEX('M2'!$B$2:$N$22,MATCH(CEILING(B2,1),'M2'!$A$2:$A$22,0),MATCH(CEILING(C2,1),'M2'!$B$1:$N$1,0))

I see a reply now from Jonmol and I'll try that next. Thanks.

Originally Posted by Jonmo1

YOu need to apply the ceiling on BOTH match values.
Just like column B on the Day1 sheet, column C also contains decimal values.
But the formatting of the cell made it DISPLAY rounded to a whole number, But the real value is still there.

In E2, put
=INDEX('M2'!$B$2:$N$22,MATCH(CEILING(B2,1),'M2'!$A$2:$A$22,0),MATCH(CEILING(C2,1),'M2'!$B$1:$N$1,0))

And that works. Thank you very much. We'll call this one solved. (don't see where I post that)

Great, glad it worked out.