Index and match function doesn´t work for all cells

vocis

New Member
Joined
Jan 15, 2020
Messages
3
Office Version
2010
Platform
Windows
Hello,

I would like to compare value in D6 with values in first column of table F10:G25 and when found just write down the value on right side in second column. I tried to use =INDEX(F10:G25,MATCH(D6,F10:F25),2) (cell F7) and then also
=INDEX(G10:G25,POZVYHLEDAT(D6,F10:F25,0),1) (which is G7), but in first case (F7) it shows correct values up to number 0.65 a writes number 275 but from value 0.7 (that should be 308) writes value above again 275, for 0.75 it writes again value one cell above 308 and value 0.8 writes one above 347 instead of 394. With function on F7 the values are correct also up to 0.65 and for 0.7 and above NOT_AVALABLE.

Can someone please help me?
Here is the sheet: Gofile - File sharing platform, anonymous and free
(Please note that it was written in czech so you might need to convert it into English version, we use ; for , and function MATCH is POZVYHLEDAT)

Thank you!
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
3,981
Office Version
365, 2019, 2016
Platform
Windows
try

=vlookup(D6,F10:G25,2,0)

or

=INDEX(F10:G25,MATCH(D6,F10:F25,0),2)

for both you need the ,0 for exact match
 

vocis

New Member
Joined
Jan 15, 2020
Messages
3
Office Version
2010
Platform
Windows
Thank you for the answer but it seems it again works only up to value 0.65 then for 0.7, 0.75 and 0.8 shows NOT_AVAILABLE (NENÍ_K_DISPOZICI)
see pics
 

Attachments

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
3,981
Office Version
365, 2019, 2016
Platform
Windows
it's probably a rounding off problem, to test it try clear 0.7 in Cells D6 and F23 then input 0.7 manually to see if it's the problem
 

Watch MrExcel Video

Forum statistics

Threads
1,090,457
Messages
5,414,637
Members
403,539
Latest member
rthompsona

This Week's Hot Topics

Top