Partial IF matches (possibly nested)

[-dkn-]

Hey guys,

I'm trying to get a partial match IF statement that checks the first two characters of a cell (likely to be numbers), and if it matches a certain string put information from another cell into the first cell.

(Explanation, I'm a network admin, making a configuration template. If the hostname entered into Cell A4 starts with say "02" for Sydney, put the Sydney username and password (defined in cell B17) into A7 (where this formula is), if it starts with 04 for Perth, put the username/password in B18 into A7... etc etc)

Thanks for any help!

[Pete_UK]

Do you have several 2-letter codes? If so, then you can use VLOOKUP, making use of a table of those codes and meanings (username/password) elsewhere on the sheet. Suppose you put that table in columns X and Y, and the cell that you want to test is A4 - then you can use this formula in B4:

=IFERROR(VLOOKUP(LEFT(A2,2),$X:$Y,2,0),"not found")

then copy down as required.

Hope this helps.

Pete

[-dkn-]

I think this is close to what I'm looking for =IFERROR(VLOOKUP(LEFT('Info sheet'!B8,2),'Location Codes'!A1:A69,2,TRUE),"not found"), but my columns of matching codes with passwords on the next sheet isn't firing, and all I get is 'not found'

[-dkn-]

Ah, got it! I wasn't checking the 2nd column properly

=IFERROR(VLOOKUP(LEFT('Info sheet'!B8,2),'Location Codes'!A1:B69,2,TRUE),"not found")

Thanks Pete, you sir, are a legend!

[Pete_UK]

If you are going to copy that formula down, you will need to make your table references absolute, i.e. $A$1:$B$69 instead of A1:B69.

Hope this helps.

Pete

P.S. If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete