Wildcard vlookup formula

[mike12345]

I am trying to do a wildcard vlookup and I'm using the below formula. I have Column D that is always unique values that are 6 characters long and I have column G that should equal column D but the data is frequently cut off. For instance Column D may have 654789 as a cell value while the corresponding cell in column G may have 789, 4789 or 54789 as it's value. I want the vlookup to recognize these values as the same. Also column H has a cash amount in it and is what should be the output when the vlookup is done.

The below formula is how i am trying to achieve this. It says If the last 3 characters of the reference cell (which would be a cell in column D and the last 3 characters is denoted by RIGHT(D14,3)) matches a cell in column G then that output should be greater than 0 since the output is just the adjacent dollar value in column H. If that is the case than simply rerun that same lookup and have the column H cell be the output, if it is not greater than 0 than that means no value was found (ie no 3 character matches between the column D cell and any of the column G cells) and to move on to the next if statement which does that same exact thing but for 4 characters to the right and if there are no 4 character matches then do it for 5 characters. the formula works for 3 characters but does not move on to 4 and 5 characters if no matches are found originally (just returns #N/A). Can someone please tell me why the formula does not move on to the 4 and 5 character arguments or even tell me a completely different way of doing what I am trying to do even if it doesn't involve the below formula? Thanks



=IF(VLOOKUP(VALUE(RIGHT(D14,3)),$G$12:$H$38,2,FALSE)>0,VLOOKUP(VALUE(RIGHT(D14,3)),$G$12:$H$38,2,FALSE),IF(VLOOKUP(VALUE(RIGHT(D14,4)),$G$12:$H$38,2,FALSE)>0,VLOOKUP(VALUE(RIGHT(D14,4)),$G$12:$H$38,2,FALSE),IF(VLOOKUP(VALUE(RIGHT(D14,5)),$G$12:$H$38,2,FALSE)>0,VLOOKUP(VALUE(RIGHT(D14,5)),$G$12:$H$38,2,FALSE),0)))

[Haseeb Avarakkan]

Try this ARRAY FORMULA, must hit CTRL+SHIFT+ENTER, rather than just ENTER.

=MAX(IF(G$12:G$38=RIGHT(D14,{3,4,5})+0,H$12:H$38))