VLOOKUP or Index Match

[mma3824]

Hello,

I would like to be able to use a formula that will look up a value and then give me back a corresponding value. I have used the VLOOKUP formula and also some Index Match formulas in the past. The issue is that the value that I want to be matching could have a different starting number in the series though all other numbers/letters in the series will be the same.

Example:
001HO12345
501HO12345

Are the same animal though I want to have an input sheet that a person can enter either of these values in it and then will link other data in a separate table to it. For your information the values on the other table will have the values stored as 001HO12345.

The easy answer is to have the person only key in the 001HO12345 and not use the other. That being said, building flexibility will be a great help.

Thank you in advance!

[RobertMika]

Attached your workbook as your request is not very clear...

[xwarlock10x]

try this

this should work for you


this sheet does everything you asked.. it can do a partial name lookup and return the results by filtering out the key data.. thus searching "501ho" returns the result of "501ho12345".. just sayin'

[mma3824]

I have attached an example of what I would like to be able to do. In column B I would like to input either form of the number 001HO or 501HO and have the columns
C - G populated with the information from the Bull tab.

Thank you for all the help!Match Example.xlsx

[newbydeveloper]

Hi mma,

Try this for the name in the table: =VLOOKUP(IF(LEFT(B2,3)=1,B2,CONCATENATE("001"&RIGHT(B2,7))),Bulls!A:B,2,FALSE)

[RobertMika]

Hmm.
So if I type lets say 250 what I will get?
I assume that HO11427 is common for 001HO11427
and 501HO11427
but 001HO11022
and 001HO11427
have the same 3 starting letter.

[newbydeveloper]

Robert,

taking your suggestion, here's the formula so it only works for 001 and 501 codes (for the name part of the table):

=IF(LEFT(B2,3)="501",VLOOKUP(CONCATENATE("001"&RIGHT(B2,7)),Bulls!A:B,2,FALSE),VLOOKUP(B2,Bulls!A:B,2,FALSE))

[azumi]

Pls check the file, something like this?

Azumi

[xwarlock10x]

try this

this should work for you


this sheet does everything you asked.. it can do a partial name lookup and return the results by filtering out the key data.. thus searching "501ho" returns the result of "501ho12345".. just sayin'

in one cell put this... this is a count of formula which turns on and off the main formula.. =SUMPRODUCT(--ISNUMBER(SEARCH(E4,$A$4:$A$25)))

make a search cell then in another cells put this then drag down.. enter it with Ctrl/SHIFT/Enter (array formula) i t will put the { } around the formula..
{=IF(ROWS(E$7:E7)>$D$4,"",INDEX($A$4:$A$25,SMALL(IF(ISNUMBER(SEARCH($E$4,$A$4:$A$25)),ROW($A$4:$A$25)-ROW($A$4)+1),(ROWS(E$7:E7)))))}

this will allow for a partial name lookup and return the results with the partial name in ANY sequence
good luck