Copying a VLOOKUP formula into another cell does not work

[tom hatten]

Hello.

I have attatched a spreadsheet with Table 1 showing a football table, with fixtures and formulas in cells AA5 and AB5 which work perfectly.

Table 2 shows another table and some forthcoming fixtures. I've copied the formula from Table 1 into cells AA37 and AB37 for Table 2 and taken into account that there are more teams in the second table.

The formula I have in Table 1, cell AA5, is:
=VLOOKUP(Y5, $B$5:$U$26,20, 0)*VLOOKUP(Z5, $B$5:$X$26, 23, 0)*$I$28

As you can see, the formulas in Table 2 do not work and I'm guessing that the problem lies with the numbers (20,0 and 23,0) but I can't figure out what they should be.

I hope someone can help me.

Thank you.

[Glenn Kennedy]

=VLOOKUP(Y5, $B$5:$U$26,20, 0) is looking in column U for a match...
VLOOKUP(Z5, $B$5:$X$26, 23, 0) is looking in column X... so is that intended?

No sheet was attached...

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[tom hatten]

I've tried the advanced option and the file is showing under Current Attachments (22.7 KB) with the option to remove it, but I can't see how to upload it.
I tried clicking the upload button on the right-hand side.

Thank you.

=VLOOKUP(Y5, $B$5:$U$26,20, 0) is looking in column U for a match...
VLOOKUP(Z5, $B$5:$X$26, 23, 0) is looking in column X... so is that intended?

I believe it is intended, yes.

[tom hatten]

Oh, there it is

[Glenn Kennedy]

OK. I'm being thick. In the lower table there was a space after Bournemouth and one before Brighton. Likewise for the second row. Also C Palace is NOT a match for
Crystal Palace...

[Glenn Kennedy]

various errors relating to the above, all removed.

[tom hatten]

Thank you very much. I really appreciate your help.

[Glenn Kennedy]

So what's wrong with it. It's taking the value for Hungerford in Column U (1.05 at row 10)
multiplying that by the value for hemel in column X (1.31 at Row 15)
and multiplying that by 1.71 the value in I28.

What DO you want it to do??

[tom hatten]

Thank you for your help, it's working just as I want it to now. One more question; is it possible to avoid returning a zero in the columns? For example, for Brighton the formula is =W50*V55*R58 returns zero because cell W50 is zero. Is there a way around this? ie, return =V55*R58 instead?

[tom hatten]

I'm sorry if I wasn't clear. I want the working formula in cells AA5 and AB5 to work in cells AA37 and AB37. The HOME formula is U*X*I58 and the AWAY formula is W*V*R58

Thank you.

[Glenn Kennedy]

AA37:

=IF(VLOOKUP(Y37, $B$37:$U$56,20, 0)=0,1,VLOOKUP(Y37, $B$37:$U$56,20, 0))*IF(VLOOKUP(Z37, $B$37:$X$56, 23, 0)=0,1,VLOOKUP(Z37, $B$37:$X$56, 23, 0))*$I$58

Basically=IF(Vlookup1=0,1,Vlookup1)*IF(Vlookup2=0,1,Vlookup2)*Fixed cell

Similarly for AB37.

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.