Editor’s Note:

 
To read more about spreadsheets, share a tip with other readers, or suggest a topic for Bill Jelen to cover in an upcoming column,
 

click here

 
to visit CFO.com’s Spreadsheet Tips Page.

In last week’s column, Greg asked about a multiconditional wizard that would generate something similar to a SUMIF. While that column explained the old Conditional Sum Wizard, reader Dominique B. writes: “It seems to me the reader’s request was not fully answered, as he was looking for a multi-condition VLOOKUP, not a sum. My recommended solution in this situation is to add an extra column containing a composite text key built using a separator unlikely to appear in the actual data (e.g., a double underscore). In your example, this composite key would be ‘C__Bob’ and using VLOOKUP you can achieve what I understand Greg T. wanted. Thanks for you column, which brings out small Excel gems.” For her suggestion, Dominique wins an autographed copy of Learn Excel from MrExcel from CFO and MrExcel.com.

There are many ways to solve most problems in Excel. The sum formula from last week’s column would handle multiple matches, but would not return text results. The methods explained in this week’s column (below) ignore multiple matches, but can return a text result. Which method you use depends on the shape of your data.

Say, for example, that you need to do a VLOOKUP to fill in the yellow range in Figure 1 (below).

Fig. 1

If you are allowed to change the original data set, you could insert a new column C (see Figure 2). Put this formula in C2 and copy down:

     =A2&”__”&B2

Fig. 2

There is nothing magic about the double underscore; Dominique used it as it is unlikely to appear anywhere in the data in columns A or B. The formula for G3 is then =VLOOKUP(G$2&”__”&$F3,$C$2:$D$27,2,False). (See Figure 3.)

Fig. 3

If you are not allowed to change the original data set to add the concatenated key, and if the original data set is sorted by company, then you can use the method illustrated in Figure 4.

Fig. 4

CFO contributing editor Bill Jelen is the author of 32 books about Excel and the host of MrExcel.com. You can win a copy of one of his books if your question or suggestion is selected for a column. Post your question at the Community Center “Ask a Question” box on the right. Tune into Bill’s next Excel Webcast, “Intro to VBA and Editing Recorded Code,” on Wednesday, May 18, at 2:00 pm.