Vlookup formula question

[cat3appr]

Hi all,

Could you help me out to create the Vlookup formula for the attached excel doc?

We are interested in the 2 last tabs on the right : " processed survey data " and " working Plough Log Data " which have a column in common, the " KP "

what I need to do, is to have the values of " burial depth" and " plough tow force " values from the " working plough log data " tab imported in the " processed survey data" tab , when the KP is the same .

the result should be a grapgh on the first tab

any input is very welcome, thank you!
Alex

[Sam Capricci]

I would think that, for your burial depth this would work... =VLOOKUP(D2,'Working (Plough Log Data)'!B:E,3,FALSE) and for plough tow force this should work... =VLOOKUP(D2,'Working (Plough Log Data)'!B:E,4,FALSE)
both dragged down (on your processed survey data sheet).
it does give matches but a lot of #N/As and that is likely because they aren't exact matching numbers i suspect.

[boopathiraja]

Try with the below formulas if the lookup value not available the result will be o

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[cat3appr]

thank you boopathiraja and Sambo Kid, i just tried to paste both the formula but they are not working.... is there a way to round up to say the 4th decimal on the lookup value?

in fact did you try to use the formula on the spreadsheet?
if i apply the formula on the burial depth, at kp 3.3956 I have a burial depth of 97.2 , I used the formula =VLOOKUP(D2,'Working (Plough Log Data)'!B:E,3,FALSE) in E:2 on the "processed survey data" tab, but I got an N/A ... but this is not possible, as I should have gotten the actual value, 97.2

[Sam Capricci]

if you are ok with resorting your working worksheet to have the values in col B ascending then you can use this vlookup for each...
burial depth =VLOOKUP($D2,'Working (Plough Log Data)'!$B:$D,3,TRUE)
Plough tow force =VLOOKUP($D2,'Working (Plough Log Data)'!$B:$E,4,TRUE)
the TRUE part works to give approximations but the reference group has to be sorted ascending.
how accurate it is for your results i don't know, you'll have to check that.

[boopathiraja]

Try

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[boopathiraja]

Try

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Sam Capricci]

now that i really look at your data, the problem is that any vlookup will return the first value it finds, so for example you have on your "working" sheet multiple instances of one KP value such as -1964.7697 but more than one burial depth and plough tow force. A vlookup will always return the first instance it finds so using my recommendation to sort columns B D and E ascending on B will always return the first burial depth of 99.4 and 12.5 and no others.
You may have to better define what you want.

[cat3appr]

guys , all the formula are not working, could you please post the spreadsheet with the working formula on it? because I pasted all the suggested ones, no one worked....

thank you!

[Sam Capricci]

here is yours back with values and formulas...

I had to delete some data so i could upload it but the concept is there.

[cat3appr]

Thank you Sambo Kid,

we are getting closer to the solution but still not there. In the doc you attached, please note that all the KP values on the "working Plough Log Data" tab has been altered and now they are all -1964.77 , and the inital tab has been removed, I think this didn't help. In fact I tried to copy and apply the formula on my spreadsheet and it didn't work.

I'm attaching again the spreadsheet, could you apply the formula on this one, without altering anything at all in the spreadsheet?

The reason why I'm doing this is because on the first tab, the one that has been removed, it should appear a graph, if the procedure has been done correctly, I'm attaching a screenshot of the graph.

If the Vlookup routine will be done correctly, this graph will appear on the first tab.

many thanks!!
Alex

[cat3appr]

please let me know if anyone will manage to solve this.
thank you!
Alex

[boopathiraja]

Hi, Check out the attachment

1) Helper1 to reduce the lookup value to 4 digit
2) Helper2 to find the diff between the lookupvalue and the result value
3) I have reduced the rows to 1000 and remove the chart since the file exceeds 1mb,

Hope it helps