VLOOKUP to From Another Sheet

swissrule wrote:
Is there a syntax error?

=VLOOKUP(H11, Total Overview :: Table 4, 2, 1)

You're missing the cell range included in the lookup table:

=VLOOKUP(A, Sheet 1::Table 1 :: $A$2:$B$6, 2)

Using your locations:

=VLOOKUP(H11, Total Overview::Table 4 :: $A$2:$B$6, 2)

The final argument ( ,1 ) specifies the default 'close match', and may be omitted.

Regards,
Barry

swissrule wrote:
...BTW I have a screenshot but I don't know how to attach it to my post. ...

You'll have to upload your screen shot to a server such as a photo hosting site; PIcasa, PhotoBucket or the like. Copy the link code for the graphic and paste it here.

Jerry

Without seeing both the error message AND the formula that generated the message, it's difficult to say with certainty what the error is. Try building your your formula by using the mouse instead of the keyboard to specify the cell and range references.

• Click on the cell where the formula is to go.
• Type (using lower case letters) =vlookup(
- VLOOKUP will change to upper case as soon as you type the (. If it doesn't, it indicates some error in the function name.
• Click cell H11 to add it to the formula.
• Type a comma.
• Click the icon for Table 4 in the Sheets list to the left to bring that table to the front.
• Click and drag to select the cells from the one containing 90 (left column) to the one containing F in the lookup table. (A2:B6 in my example)
- The formula editor should now contain =VLOOKUP(H11,Total Overview::Table 4::A2:B6
- with H11 in a blue oval and the everything to the right of the comma in an orange oval.
• Hover the mouse pointer over the right end of the orange oval, Click on the triangle that appears, and choose ($A$2) absolute row and column
• Repeat, and choose ($B$6) absolute row and column
• Type ,2)
• Click the green checkmark (or press return) to accept the formula.
- Depending on the value in H11, you should see one of the letters A, B, C, D or F in the cell containing the formula.

swissrule wrote:
It says:

"A2:B6" isn't a valid reference.

As far as I know, this means that there is no such range in the table.
Maybe there is a single column or maybe there is no row 6.

Yvan KOENIG (VALLAURIS, France) vendredi 3 décembre 2010 10:39:43

Hello

Next time reduce more the picture size, we are all owning a microscope 😟

Here, the formula :
=VLOOKUP(H11,Total Overview t:: Table 4 :: $A:$B,2,1)

behave flawlessly.

Yvan KOENIG (VALLAURIS, France) vendredi 3 décembre 2010 21:05:59

Table 4 has only five rows, not six, so you will get an error if you try to reference a range that includes row 6. I'd recommend Yvan's formula which references columns A and B in their entirety rather than specifying a range of columns and rows. Simpler that way.

Can you post your Numbers document, not jpgs, for us to look at? I've mentioned in other posts that I sometimes have problems with Numbers not recognizing a correct range and giving me an error where there should be none. I have sometimes had to use a different range to clear things up then gone back and changed it to the correct one. That may be the case here but I can't say for sure unless I can try out the document.

Hi Monica,

I see two errors.

On the table "Write", the following change is needed in H12:

H12: current formula: =VLOOKUP(H11,Total Overview t:: Table 4 :: $A:$B,2,1)

H12: corrected formula: =VLOOKUP(H11,Total Overview:: Table 4 :: $A:$B,2,1)

The second error is a misunderstanding of the meaning of "100%". "100% of a pie" means "one whole pie." In money terms, 100% of 1.00 CHF is 100 centisimi (or centimes, depending on the canton), not 100 CHF.
(Edit: or substitute $1.00 and 100¢ now that I've read your location as well as your username.)

The numbers on your lookup table must have the same meaning as the numbers calculated in column H of the various tables. They don't.

For the values listed in the lookup table and the value in H11 on this table, F is the correct grade.

The score calculated by the formula in H2, =(AVERAGE(B2:G2))/5 is the average of

5, 5, 5, 5, 5, and 5, divided by 5.

The average of that set of numbers is 5. Divided by 5, the result is 1, which is well below 60.

Correction needed:

Revise the values on the lookup table to

0.9 A
0,8 B
0.7 C
0.6 D
0 F

or

90% A
80% B
...
0 F

OR

Revise the formula in H2 to

=(AVERAGE(B2:G2))*100/5

and format all cells now showing percents as Number rather than as Percentage.

Regards,
Barry

Message was edited by: Barry