Nic, I really kind of thought someone would chime in before now. What you
want is the VLOOKUP() function. Look at Excel's Help on the topic for more
details than what I provide here.
The basic format of the command is like this:
=VLOOKUP($A$1,$C$1:$G$100,3,False)
$A$1 would be a cell with a person's name in it that you want information
about. Then $C$1:$G$100 might be the table you showed, with names in column
C. This table can be on another sheet and would be referenced like:
'Sheet Name'!$C$1:$G$100
Of course the columns and rows involved can vary.
The ",3," part of it is the trick. This is the piece that says: when you
find a match (to A1,) in the first column of the table (column C) then return
the value from the 3rd column of that table on that row, which would be from
column E. The "False" says that the list of names in the table do not have
to be in order.
So you could have a cell ($A$1) for the name on your invoice sheet then
VLOOKUP() formulas in all the other cells to bring over the address, phone #,
email, etc. along with the invoice amount. Just by changing that ",3," piece
of the formula. The $ symbols are to keep the addresses from changing as you
copy and modify the formula into the various cells on your invoice sheet.
Now, VLOOKUP() will return a #N/A error if it does not find a match in the
table. That might be ok in this case because it will clue you in very quickly
that you typed the name into cell A1 wrong. But lots of times people do not
want to see a bunch of #N/A entries cluttering up a worksheet, so we can hide
those by 'wrapping' the formula in an error trap like this:
=IF(ISNA(VLOOKUP($A$1,$C$1:$G$100,3,False)),"",VLOOKUP($A$1,$C$1:$G$100,3,False))
so when an #N/A would have appeared, the cell remains empty looking, or you
can put words or values in place of the ,"", like this:
=IF(ISNA(VLOOKUP($A$1,$C$1:$G$100,3,False)),"No match
found",VLOOKUP($A$1,$C$1:$G$100,3,False))
or to return a value of zero instead of the error:
=IF(ISNA(VLOOKUP($A$1,$C$1:$G$100,3,False)),0,VLOOKUP($A$1,$C$1:$G$100,3,False))
Hope this helps you out.