Monday, May 21, 2007

Quick Answers to Searcher's Questions

Can you use concatenate in a VLOOKUP?
The answer appears to be YES and NO.

=VLOOKUP(CONCATENATE(A6,B6),'Table Array'!$A$2:$D$6,4,0)

Your lookup_value CAN be a formula, just like you see above. It does not appear that your table array can include formulas, though. I suggest that you concatenate your lookup value in the VLOOKUP, and then create a column for the same concatenation on your sheet containing the table array. This is a good question, though, and I can see how my solution might not be satisfactory. I'll keep looking into this one.

How do I make a table array absolute?
Example: table array is NOT absolute
=VLOOKUP(CONCATENATE(A6,B6),'Table Array'!A2:D6,4,0)

Example: table array IS absolute
=VLOOKUP(CONCATENATE(A6,B6),'Table Array'!$A$2:$D$6,4,0)

The only difference is that you add the dollar signs in front of the cell's column and row references. I recommend, when in doubt, always make the table array absolute. I can't think of any reason not to. But, if it isn't absolute, you may not get the results you expect. So, always absolute!

Excel Label Formatting:
Someone searched on this and found me, but did not actually ask a question. I'm going to assume the question refers to mailing labels or something like that. My suggestion is to make your list in excel, and use Mail Merge in Word. I'm not an expert there, but I have used it, and it works very nicely.

Choosing from a Number list
What this searcher is looking for is data validation. Click on the cell where you want to be able to choose from a number list, and then click data-->validation. This brings up a dialogue box. Under validation criteria, choose List. In source box, enter the cells that contain your list of numbers, or type the number in yourself, separated by commas. If you want your list of numbers to be somewhere on the worksheet, just highlight them. If you want them on a different sheet in your workbook, you have to name the range, and then make your list equal to your named range. Since this is a quick answer, I'll say look at Excel Help for named ranges to figure out how to make this happen. Leave me a comment or send me an email if you need an explanation on this.

Change the format of phone numbers in Excel
Do this by bringing up the format cell box. Now, on the number tab, click on custom. Type in the custom format in the Type box. Let's say your format is currently:
###-###-####
This would format 5551112222 as 555-111-2222
Change the type so that it looks like this:
(1)-###-###-####
Now, your phone number looks like this:
(1)-555-111-2222

Another searcher asks about:
Excel Function to Group like items
The quick answer is Pivot Tables. If you delve into this on your own, let me say that 1)They are awesome 2) They always work. 3) They're kinda simple, really 3)They don't really act like Excel and that makes them seem complex.

I plan on covering a whole set of Pivot Table topics as the days go by, so more on this later.

Today, a searcher asked about what seems like conditional formatting. He or she searched on:
If this column date less than show as color excel
They found me on the 7th page of their Google Search. I hope they found what they are loooking for and disovered the thing to do is use Conditional Formatting, Formula is Option.

No comments: