"lookup" formulas

[Guest]

Please could someone help me with look up tables.

I have 2 different worksheets within one spreadsheet. The one worksheet
contains an account number eg 00 00000 000 with an account name in the column
adjacent to the account number. The other shows just the account number and
payment amount. Is there any formula i could use which looks up the account
name from the one worksheet and adds the account name next to the number in
another worksheet?

I have thousands of entries to complete so any help at all would br brilliant.
Thanks

 

[Pete_UK]

Assume your table with the account code and name is on Sheet1,
occupying A1 to B1000. Assume the other sheet is called Sheet2, and
has account code in column A and payment amount in column B.

First of all, insert a new column B in Sheet2, and enter this formula
in B2, assuming your data starts in A2 - maybe with a header row:

=VLOOKUP(A2,Sheet1!A$1:B$1000,2,0)

Adjust the ranges to suit your table. Copy this formula down column B
for as many items as you have in column A.

Hope this helps.

Pete

 

[Guest]

Appreciate your help.

I have set the spreadsheet and formulas up exactly as you have described
below but i am getting the following erroe reference in cell B2 worksheet 2-
#N/A.

Are you able to describe what each part of the formula refers to
specifically to help my understanding of it? Please could you break it down?

Thanks

 

[Guest]

I'll try to fill in for Pete_UK for a minute.

The #N/A means that the value you were looking for was not found in the
source list.

In VLOOKUP() (as explained in Excel Help on the subject)
Using the formula Pete gave for reference:
the first parameter is what you are looking for (whatever is displayed in A2
on the current sheet)
the second parameter is where to look for it, in this case it is going to
look for it in column A of a matrix that goes from A1 to B1000 on Sheet1.
The third parameter, 2 - says when you find a match for this, return the
value in the second column of the lookup array.
Finally, the ,0) says that your list in the lookup array, column A, does not
have to be sorted in any particular order.

I suppose the question at this point is, assuming you put the formula on
Sheet2 in cell B2 exactly as Pete_UK gave it to you:
What is in cell A2 on Sheet2, and is it definitely a match for an entry in
Column A (rows 1 through 1000) on Sheet1 ?? Since you're getting #N/A, I'm
assuming either nothing or a typo in A2 on Sheet2.

Now, if the value in A2 on Sheet2 should NOT be on Sheet1 in cells A1:A1000,
and you want to keep the #N/A from showing up, write it this way
=IF(ISNA(VLOOKUP(A2,Sheet1!A$1:B$1000,2,0)),"",VLOOKUP(A2,Sheet1!A$1:B$1000,2,0)))

that says that if when attempting to do the VLOOKUP an #N/A error takes
place, just output an empty string ( ,"", in the formula), but if things
worked ok, then show me the result.

 

[Guest]

Thank you for your very detailed response. I made a few adjustments to the
formula and found the errors. One error which i do not fully understand is
that one of the cells I was evaluating contained a constant?

Anyway, I have learnt a lot and have been able to get the formula's to work
thanks to yours and Pete_UK's response.

Thanks again, very much appreciated!!!!

 

[Pete_UK]

You're welcome - thanks for feeding back.

And thanks to JL for such a detailed explanation while I was
asleep !!.

Pete