Vlookup issue .

[Sunline]

Hello all , im having a problem trying to get my Vlookup function to acknowledge all data in cols EJ and EK .

As per sample sheet o zeros are being found . But anything else isnt . ???? .
Seems i have a problem with my decimals somewhere , have tried changing formats etc to no avail .
Any help appreciated .
Thanks .

Excel Workbook
	DW	DX	DY	EJ	EK	EL	EM
1	Marg3	Marg2	Marg1	M3/M2	M2/M1	ResultcolEJ	ResultcolEK
2				0.0	0.0	0	0
3	5.6	26.0	6.7	-20.4	19.3	#N/A	#N/A
4				0.0	0.0	0	0
5				0.0	0.0	0	0
6	5.9	1.0	10.9	4.9	-9.9	#N/A	#N/A
7	6.2	4.8	1.8	1.4	3.0	#N/A	#N/A
8				0.0	0.0	0	0
9		4.0	20.9	-4.0	-16.9	#N/A	#N/A
10	10.9	6.3	27.0	4.6	-20.7	#N/A	#N/A
Sheet1

 

[Robert Mika]

#N/A indicates that there are not matching values.
What's your data in Sheet2?
Are they in the same format as the on this sheet?
(I mean they are not in Text format)?
maybe you have got any spaces befor or after each value.
Try manualy compare a cell that you are sure of exist in the Sheet2 --- EJ3=Sheet2!A3(like this)
To see if you get TRUE or FALSE.

 

[Sunline]

Thanks for that Robert .
On checking my sheet2 is not matching .
I checked 20.4 in cell EJ3 but up top where functions are inserted it says 20.40000000023
All the N/A,s seem to like this .
I tryed for hours to change formats , even changing the sheet to values (no functions) still no joy .
Thanks .

 

[Robert Mika]

20.4 is not 20.40000000023.

Not sure if that helps but here is an example how you can round the value in your Sheet2 column:
Excel 2010

	A	B	C	D	E	F	G
1		20.40000000023	DESFAS		20.4		DESFAS

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula G1 {=INDEX($C$1:$C$13,MATCH(E1,ROUND($B$1:$B$13,2),0))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Give a go and comeback to us.

 

[Sunline]

Im missing the plot here Robert , where do i enter this function and should i be putting all the DESFAS in there too .
Cant get this to work .

When i made my sheet2 im entering example 999.9 then 999.8 etc , but 20 numbers on they start adding 0000000023 or similar .

 

[Robert Mika]

Im missing the plot here Robert , where do i enter this function and should i be putting all the DESFAS in there too .
Cant get this to work .

When i made my sheet2 im entering example 999.9 then 999.8 etc , but 20 numbers on they start adding 0000000023 or similar .

Sorry for confusion I mention that this is an example.
Try this:

=IF(EJ2="",0,INDEX($B$2:$B$19992,MATCH(EJ2,ROUND($A$1:$A$1992,0),0)))
confirm Control+Shift+Enter

 

[Sunline]

Still getting N/A .
This appears to be looking at col B which doesnt need referencing .
Thanks .

 

[Robert Mika]

Still getting N/A .
This appears to be looking at col B which doesnt need referencing .
Thanks .

Your VLOOKUP formula is:
VLOOKUP(EK10,Sheet2!$A$2:$B$19992,2,0))

so EK is looking at column A to get value from column B(the third argument is 2)
In my formula MATCH function is taking EK , looking at column A and the INDEX returns value from column B.
If you still getting #NA?
Did you confirm this formula with Control+Shift+Enter?

Try again wtih this:
=IF(EJ2="",0,INDEX(Sheet2!$B$2:$B$19992,MATCH(EJ2,ROUND(Sheet2!$A$2:$A$19992,2),0)))

or this(that will prevent to return #N/A if there is no matching value at all)
=IF(EJ2="",0,IF(ISNA(MATCH(EJ2,ROUND(Sheet2!$A$2:$A$19992,2),0)),"",INDEX(Sheet2!$B$2:$B$19992,MATCH(EJ2,ROUND(Sheet2!$A$2:$A$19992,2),0))))

 

[Sunline]

I applied your 1st formular
=IF(EJ2="",0,INDEX(Sheet2!$B$2:$B$19992,MATCH(EJ2,ROUND(Sheet2!$A$2:$A$19992,2),0)))
Got N/A still .

Using this one
=IF(EJ2="",0,IF(ISNA(MATCH(EJ2,ROUND(Sheet2!$A$2:$A$19992,2),0)),"",INDEX(Sheet2!$B$2:$B$19992,MATCH(EJ2,ROUND(Sheet2!$A$2:$A$19992,2),0))))

As you mentioned not now getting N/A , as you know they are blank .
I can 100% state the numbers mentioned in cols EJ or EK are in sheet 2 .
If i manually change and type in myself in either of these cols where there is a N/A from the Vlookup , the N/A then disappears .

 

[Robert Mika]

Did you confirm the formula with Control+shift+Enter?
Otherwise please PM me.