Vlookup issue .

Sunline

Well-known Member
Joined
Oct 6, 2007
Messages
675
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
DWDXDYEJEKELEM
1Marg3Marg2Marg1M3/M2M2/M1ResultcolEJResultcolEK
20.00.000
35.626.06.7-20.419.3#N/A#N/A
40.00.000
50.00.000
65.91.010.94.9-9.9#N/A#N/A
76.24.81.81.43.0#N/A#N/A
80.00.000
94.020.9-4.0-16.9#N/A#N/A
1010.96.327.04.6-20.7#N/A#N/A
Sheet1
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
#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

Well-known Member
Joined
Oct 6, 2007
Messages
675
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

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
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
ABCDEFG
120.40000000023DESFAS20.4DESFAS

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

Array Formulas
CellFormula
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

Well-known Member
Joined
Oct 6, 2007
Messages
675

ADVERTISEMENT

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

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
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

Well-known Member
Joined
Oct 6, 2007
Messages
675

ADVERTISEMENT

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

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
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

Well-known Member
Joined
Oct 6, 2007
Messages
675
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

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
Did you confirm the formula with Control+shift+Enter?
Otherwise please PM me.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,125
Messages
5,599,843
Members
414,342
Latest member
K Darrell Smith

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top