Merging 2 datasets with vlookup to a higher or lower value?

nicorponic

New Member
Joined
Jun 8, 2015
Messages
1
I have been struggling to find a formula that works for the example below (see question mark!). What i am trying to do is merge 2 datasets. The problem is that the common identifiers are not always exactly the same but quite similar nevertheless. I tried using the vlookup function making the last argument of the function (lookup range) true or using the number 2 instead (in an attempt to add 2 to the identifier, but the function returns me the value of the closest identifier (that is 7800).
1st dataset :
Identifier =>0000000161200103 price =>7787
Identifier =>0000000161200712 price =>7800
Identifier =>0000001961200803 price =>9000
2nd dataset :
Identifier =>0000000161200103 price =>7787
Identifier =>0000000161200801 price => ????? returns either the closest (7800) or #N/A.
What i need to be returned is the value 9000. Ideally, I need to be able to manipulate somehow the lookup value range that vlookup searches (upward or downward) or what to do next with the lookup value if there is no exact match.. maybe with an if function or iferror of isna...but i just cannot get there.
The functions i tried are the following: =VLOOKUP($A2;A2:B4;1;TRUE) -> returns cell B3 =VLOOKUP($A2;A2:B4;1;1) -> returns cell B3 =VLOOKUP($A2;A2:B4;1;2) -> returns cell B3 =IF(ISNA(VLOOKUP($A2;A2:B4;1;1;FALSE)) = TRUE; VLOOKUP(($A2+2);A2:B4;1;FALSE); VLOOKUP($A2;A2:B4;1;FALSE)) -> returns #N/A
Any help deeply appreciated!
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Forum statistics

Threads
1,214,429
Messages
6,119,424
Members
448,896
Latest member
MadMarty

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
Back
Top