Hi This is my first post on here so I would first like to thank the people on here that give up their time to help others...brilliant!

I'm trying to create a spreadsheet that looks at a cell that contains both text and numbers. Examples of cell entries are: B20, B25, and so on. Both the letter and the number can change which means that nested IF will be unsuitable due to there being more than 7 permutations. I usually use & in my formulas as this enables me to have unlimited permutations: =IF($F16="UC",VLOOKUP($G16,UC!$D$8:$X$38,2, FALSE),"")&IF($F16="UB",VLOOKUP($G16,UB!$D$8:$X$88,2, FALSE),""

My question is, how can I lookup a cell value that contains both text and a numeric value?

A =Vlookup() will function just fine using Text Strings or Numbers. If your data looks like:

A B 1) B20 Alpha 2) B25 Beta 3) F500 DeltaIn cell C1 enter the Text String B25

In cell D1 enter the formula: =VLOOKUP(C1,A1:B3,2,FALSE)

and you should get Beta as your answer.MIKE

Thanks Mike; feel really guilty as I just assumed there would be a problem with using text and numbers. Moral of this tale: try to do it first instead of assuming it won't work and relying on other people to do the work for you!!

Ask Your Question

Weekly Poll