CODEWITHSUNDEEP
excelvlookup
G.M.
G.M.

Reputation: 43

Using VLOOKUP for Multiple Parts of a Cell

I am trying to create a decoding macro. I have different combinations of letters in each cell on one sheet. For example, in cell B2 I would have something like "ABC." On a different sheet I have a table that matches letters to numbers, so I want the output in the new cell to be "123" in that case. I know how to use VLOOKUP on an entire cell, but cannot figure out how to use it on individual parts and then concatenate the results back together in the new cell.

This is what I've figured out so far. I think I need INDIRECT as part of it so I can reference the cell, but I cannot figure out how to look up the different portions of the cell. I do not want to create new columns to split the letter combinations up if possible.

=IFERROR(VLOOKUP("not sure??",'Conversion Table'!A4:B19,2,FALSE),"")

Thanks!

Upvotes: 1

Views: 118

Answers (2)

IgorKB
IgorKB

Reputation: 38

I'm assuming your cell B2 is limited to 3 chars only, and it's the same everywhere. In this case, you can do:

=CONCATENATE(VLOOKUP(MID(B2,1,1),'Conversion Table'!$A$4:$B$19,2,0),VLOOKUP(MID(B2,2,1),'Conversion Table'!$A$4:$B$19,2,0),VLOOKUP(MID(B2,3,1),'Conversion Table'!$A$4:$B$19,2,0))

If you have more chars, only add them using concatenate and select them one by one using MID.

Edit - locked the lookup table.

Upvotes: 1

tpkaplan
tpkaplan

Reputation: 312

I think what you may be looking for is this:

     A      B                    C                        D

1                                                        =""

2          ABC       =IFERROR(VLOOKUP(                   =D1&C2
                      B2,
                      'Conversion Table'!$A$4:$B$19,
                      2,FALSE),"")

3          XYZ       =IFERROR(VLOOKUP(                   =D2&C3
                      B3,
                      'Conversion Table'!$A$4:$B$19,
                      2,FALSE),"")

4          PQR       =IFERROR(VLOOKUP(                   =D3&C4
                      B4,
                      'Conversion Table'!$A$4:$B$19,
                      2,FALSE),"")

5          DEF       =IFERROR(VLOOKUP(                   =D4&C5
                      B5,
                      'Conversion Table'!$A$4:$B$19,
                      2,FALSE),"")

The "Final Answer" appears in cell D5

Upvotes: 1

Related Questions