Spreadsheet: 73000d1273673617-excel-2007-something-beyond-vlookup-scratch
Sheet: Sheet1

wl | spectrum 1 | spectrum 2, interpolated |  | wl | spectrum 2 |  | wl of spectrum 1 | spectrum 2, with vlookup
400 | 0.009904113975321543 | 0.0162487223033933 |  | 400.82 | 0.01630168863785697 |  | 400 | #N/A |  | wl = wavelength
402 | 0.01005931813503237 | 0.016377908485012 |  | 402.55 | 0.01641343468495714 |  | 402 | 0.01630168863785697 |  | what I want to do is to extract the exact y-values of spectrum 2 that correspond
404 | 0.01021389121551151 | 0.01653062140504939 |  | 404.27 | 0.01655244238051484 |  | 404 | 0.01641343468495714 |  | to the wl of spectrum 1.
406 | 0.01036823537240562 | 0.01668201568258888 |  | 405.99 | 0.01668123570281246 |  | 406 | 0.01668123570281246 |  | example, for wl = 414, VLOOKUP would return either E9 or E10
408 | 0.01052224815524663 | 0.01683738421282677 |  | 407.71 | 0.01681539222435726 |  | 408 | 0.01681539222435726 |  | but what I want is to interpolate between E9 and E10, and return that
410 | 0.01067642554142965 | 0.01699750947262063 |  | 409.43 | 0.01694582746631437 |  | 410 | 0.01694582746631437 |  | interpolated value.
412 | 0.01082979430061017 | 0.01717085754347691 |  | 411.15 | 0.01710178018709819 |  | 412 | 0.01710178018709819
414 | 0.01098285985019438 | 0.01732103082320897 |  | 412.86 | 0.01724074757463657 |  | 414 | 0.01724074757463657
416 | 0.01113554858525713 | 0.01747323749481035 |  | 414.57 | 0.01736117244749518 |  | 416 | 0.01736117244749518
418 | 0.01128744590742024 | 0.01764031275873162 |  | 416.28 | 0.01749518030127765 |  | 418 | 0.01763948116739443
420 | 0.01143844695085405 | 0.01780631927796253 |  | 417.99 | 0.01763948116739443 |  | 420 | 0.01778168328605325
422 | 0.01158844681826574 | 0.01797227496279614 |  | 419.7 | 0.01778168328605325 |  | 422 | 0.01792210843993614
424 | 0.01173809034299132 | 0.01814845713628268 |  | 421.41 | 0.01792210843993614 |  | 424 | 0.01806665604817684
426 | 0.01188734133755465 | 0.01834432641559412 |  | 423.11 | 0.01806665604817684 |  | 426 | 0.0182229053175925
428 | 0.01203576822166407 | 0.01853955670116671 |  | 424.81 | 0.0182229053175925 |  | 428 | 0.01839636402902338
430 | 0.01218289495130612 | 0.01877242324709012 |  | 426.51 | 0.01839636402902338 |  | 430 | 0.01876237983443383
432 | 0.01232904236455845 | 0.0189950130120111 |  | 428.21 | 0.01855973821871711 |  | 432 | 0.01895208874016382
434 | 0.01247371348656817 | 0.01920116893662999 |  | 429.91 | 0.01876237983443383 |  | 434 | 0.01913809391816867
436 | 0.01261681022084758 | 0.0193971707978073 |  | 431.61 | 0.01895208874016382 |  | 436 | 0.01929037503416816
438 | 0.01275823574993328 | 0.01958802790176857 |  | 433.3 | 0.01913809391816867 |  | 438 | 0.019469072896099
440 | 0.01289789471252256 | 0.01977893966564645 |  | 434.99 | 0.01929037503416816 |  | 440 | 0.01962137135032746
442 | 0.0130366573875923 | 0.01996320033595594 |  | 436.68 | 0.019469072896099 |  | 442 | 0.01994228573825875
444 | 0.01317351732029125 | 0.02012846762013139 |  | 438.37 | 0.01962137135032746 |  | 444 | 0.02008366841869173
446 | 0.01330889173796095 | 0.02028668641691195 |  | 440.06 | 0.01978473972633304 |  | 446 | 0.02021806602301072
448 | 0.01344169159671538 | 0.02044444511642029 |  | 441.75 | 0.01994228573825875 |  | 448 | 0.0203490685931858
450 | 0.01357285783485656 | 0.02058114028159522 |  | 443.44 | 0.02008366841869173 |  | 450 | 0.02048259572571409
452 | 0.01370397904652349 | 0.02071237699545642 |  | 445.12 | 0.02021806602301072 |  | 452 | 0.02070201428639824
454 | 0.01383512760609478 | 0.02084275301564852 |  | 446.8 | 0.0203490685931858 |  | 454 | 0.02081082273150913
456 | 0.01396580989022083 | 0.02096497276732012 |  | 448.48 | 0.02048259572571409 |  | 456 | 0.02092257872599699
458 | 0.01409431608000288 | 0.0210699928048219 |  | 450.16 | 0.0205915133927406 |  | 458 | 0.02101107628725903
460 | 0.01421997564379464 | 0.02116987338992352 |  | 451.84 | 0.02070201428639824 |  | 460 | 0.02109814760082539
462 | 0.01434389825847267 | 0.02126266258466751 |  | 453.52 | 0.02081082273150913 |  | 462 | 0.0212582158606062
464 | 0.01446667765451714 | 0.02134430153093447 |  | 455.2 | 0.02092257872599699 |  | 464 | 0.02132572521680957
466 | 0.01458832646263042 | 0.02142767571144554 |  | 456.87 | 0.02101107628725903 |  | 466 | 0.02139623077269272
468 | 0.01470691071821494 | 0.02150713512379443 |  | 458.54 | 0.02109814760082539 |  | 468 | 0.02146402116013387
470 | 0.01482299210678055 | 0.0215860347770945 |  | 460.22 | 0.02118068138554104 |  | 470 | 0.02152888631266823
472 | 0.01493786351460924 | 0.02166024343225097 |  | 461.89 | 0.0212582158606062 |  | 472 | 0.02165601902594165
474 | 0.01504947974301886 | 0.02173406366080109 |  | 463.56 | 0.02132572521680957 |  | 474 | 0.02171976915751872
476 | 0.01515844967622939 | 0.0218018919724371 |  | 465.23 | 0.02139623077269272 |  | 476 | 0.02177249999184922
478 | 0.01526543698852144 | 0.02187808408007872 |  | 466.89 | 0.02146402116013387 |  | 478 | 0.02183426035612248
480 | 0.01537043164451665 | 0.0219590565487803 |  | 468.56 | 0.02152888631266823 |  | 480 | 0.0218986385700759
482 | 0.01547490760003147 | 0.0220433723255644 |  | 470.22 | 0.02159476579249296 |  | 482 | 0.02203791896656066
484 | 0.0155766753594109 | 0.02211856300647582 |  | 471.89 | 0.02165601902594165 |  | 484 | 0.02209790591560185
486 | 0.01567569270351016 | 0.02219585202700895 |  | 473.55 | 0.02171976915751872 |  | 486 | 0.02216648745730342
488 | 0.01577192001671993 | 0.02228708712757035 |  | 475.21 | 0.02177249999184922 |  | 488 | 0.02222416786208213
490 | 0.01586690395744194 | 0.02237018322583962 |  | 476.87 | 0.02183426035612248 |  | 490 | 0.02231140886044816
492 | 0.01595585919680007 | 0.02244918370142069 |  | 478.53 | 0.0218986385700759 |  | 492 | 0.02243954229774562
494 | 0.01604432127212545 | 0.02253350568901301 |  | 480.19 | 0.02196686567527951 |  | 494 | 0.02250582694801171
496 | 0.01612740031395528 | 0.02262336737139794 |  | 481.85 | 0.02203791896656066 |  | 496 | 0.02258323359657467
498 | 0.01620664387130363 | 0.02270729166364081 |  | 483.5 | 0.02209790591560185 |  | 498 | 0.02265368118004104
500 | 0.0162871026419949 | 0.0227920101511419 |  | 485.16 | 0.02216648745730342 |  | 500 | 0.0227920101511419
 |  |  |  | 486.81 | 0.02222416786208213
 |  |  |  | 488.46 | 0.02231140886044816
 |  |  |  | 490.11 | 0.02237438139479615
 |  |  |  | 491.76 | 0.02243954229774562
 |  |  |  | 493.41 | 0.02250582694801171
 |  |  |  | 495.06 | 0.02258323359657467
 |  |  |  | 496.71 | 0.02265368118004104
 |  |  |  | 498.36 | 0.02272225272883144
 |  |  |  | 500 | 0.0227920101511419