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!
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!