Spreadsheet: 72616d1273311094-excel-2007-something-beyond-vlookup-book2
Sheet: Sheet1

wl | spectrum 1 |  | wl | spectrum 2 |  | wl of spectrum 1 | spectrum 2, with vlookup
400 | 0.009904113975321543 |  | 400.82 | 0.01630168863785697 |  | 400 |  |  | wl = wavelength
402 | 0.01005931813503237 |  | 402.55 | 0.01641343468495714 |  | 402 |  |  | what I want to do is to extract the exact y-values of spectrum 2 that correspond
404 | 0.01021389121551151 |  | 404.27 | 0.01655244238051484 |  | 404 |  |  | to the wl of spectrum 1.
406 | 0.01036823537240562 |  | 405.99 | 0.01668123570281246 |  | 406 |  |  | example, for wl = 414, VLOOKUP would return either E9 or E10
408 | 0.01052224815524663 |  | 407.71 | 0.01681539222435726 |  | 408 |  |  | but what I want is to interpolate between E9 and E10, and return that
410 | 0.01067642554142965 |  | 409.43 | 0.01694582746631437 |  | 410 |  |  | interpolated value.
412 | 0.01082979430061017 |  | 411.15 | 0.01710178018709819 |  | 412
414 | 0.01098285985019438 |  | 412.86 | 0.01724074757463657 |  | 414
416 | 0.01113554858525713 |  | 414.57 | 0.01736117244749518 |  | 416
418 | 0.01128744590742024 |  | 416.28 | 0.01749518030127765 |  | 418
420 | 0.01143844695085405 |  | 417.99 | 0.01763948116739443 |  | 420
422 | 0.01158844681826574 |  | 419.7 | 0.01778168328605325 |  | 422
424 | 0.01173809034299132 |  | 421.41 | 0.01792210843993614 |  | 424
426 | 0.01188734133755465 |  | 423.11 | 0.01806665604817684 |  | 426
428 | 0.01203576822166407 |  | 424.81 | 0.0182229053175925 |  | 428
430 | 0.01218289495130612 |  | 426.51 | 0.01839636402902338 |  | 430
432 | 0.01232904236455845 |  | 428.21 | 0.01855973821871711 |  | 432
434 | 0.01247371348656817 |  | 429.91 | 0.01876237983443383 |  | 434
436 | 0.01261681022084758 |  | 431.61 | 0.01895208874016382 |  | 436
438 | 0.01275823574993328 |  | 433.3 | 0.01913809391816867 |  | 438
440 | 0.01289789471252256 |  | 434.99 | 0.01929037503416816 |  | 440
442 | 0.0130366573875923 |  | 436.68 | 0.019469072896099 |  | 442
444 | 0.01317351732029125 |  | 438.37 | 0.01962137135032746 |  | 444
446 | 0.01330889173796095 |  | 440.06 | 0.01978473972633304 |  | 446
448 | 0.01344169159671538 |  | 441.75 | 0.01994228573825875 |  | 448
450 | 0.01357285783485656 |  | 443.44 | 0.02008366841869173 |  | 450
452 | 0.01370397904652349 |  | 445.12 | 0.02021806602301072 |  | 452
454 | 0.01383512760609478 |  | 446.8 | 0.0203490685931858 |  | 454
456 | 0.01396580989022083 |  | 448.48 | 0.02048259572571409 |  | 456
458 | 0.01409431608000288 |  | 450.16 | 0.0205915133927406 |  | 458
460 | 0.01421997564379464 |  | 451.84 | 0.02070201428639824 |  | 460
462 | 0.01434389825847267 |  | 453.52 | 0.02081082273150913 |  | 462
464 | 0.01446667765451714 |  | 455.2 | 0.02092257872599699 |  | 464
466 | 0.01458832646263042 |  | 456.87 | 0.02101107628725903 |  | 466
468 | 0.01470691071821494 |  | 458.54 | 0.02109814760082539 |  | 468
470 | 0.01482299210678055 |  | 460.22 | 0.02118068138554104 |  | 470
472 | 0.01493786351460924 |  | 461.89 | 0.0212582158606062 |  | 472
474 | 0.01504947974301886 |  | 463.56 | 0.02132572521680957 |  | 474
476 | 0.01515844967622939 |  | 465.23 | 0.02139623077269272 |  | 476
478 | 0.01526543698852144 |  | 466.89 | 0.02146402116013387 |  | 478
480 | 0.01537043164451665 |  | 468.56 | 0.02152888631266823 |  | 480
482 | 0.01547490760003147 |  | 470.22 | 0.02159476579249296 |  | 482
484 | 0.0155766753594109 |  | 471.89 | 0.02165601902594165 |  | 484
486 | 0.01567569270351016 |  | 473.55 | 0.02171976915751872 |  | 486
488 | 0.01577192001671993 |  | 475.21 | 0.02177249999184922 |  | 488
490 | 0.01586690395744194 |  | 476.87 | 0.02183426035612248 |  | 490
492 | 0.01595585919680007 |  | 478.53 | 0.0218986385700759 |  | 492
494 | 0.01604432127212545 |  | 480.19 | 0.02196686567527951 |  | 494
496 | 0.01612740031395528 |  | 481.85 | 0.02203791896656066 |  | 496
498 | 0.01620664387130363 |  | 483.5 | 0.02209790591560185 |  | 498
500 | 0.0162871026419949 |  | 485.16 | 0.02216648745730342 |  | 500
 |  |  | 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