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

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History