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

#### nicorponic

##### New Member
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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Replies
7
Views
758
Replies
2
Views
179
Replies
1
Views
766
Replies
16
Views
725
Replies
1
Views
180

1,203,625
Messages
6,056,387
Members
444,862
Latest member
more_resource23

### 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.

### Which adblocker are you using?

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

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