# Thread: Troublw with nested If statements...cant figure out vlookup, or renaming

1. ## Troublw with nested If statements...cant figure out vlookup, or renaming

Im having issues with figuring another formula to use other than the IF statement. Excel 2007 will not allow me to use more than 7. My spreadsheet has a column that references several other tabs in the same worksheet. Maybe this will help:

=IF(\$E\$9="Bidder #1",G107,IF(\$E\$9="Bidder #2",I107,IF(\$E\$9="Bidder #3",K107,IF(\$E\$9="Bidder #4",M107,IF(\$E\$9="Bidder #5",O107,IF(\$E\$9="Bidder #6",Q107,IF(\$E\$9="Bidder #7",S107,IF(\$E\$9="Bidder #8",U107,IF(\$E\$9="Bidder #9",W107,IF(\$E\$9="Bidder #10",Y107,IF(\$E\$9="CRI BID DAY",D107," ")))))))))))

Where Bidder#1-10 are in different columns.

BIDDER #1 BIDDER #2 BIDDER #3 etc....

There is a dial that I can push to choose which bidder is lowest and if they have any alternates It will show up on the tab with the formula above. Unfortunately excel wont allow me to use this formula. Any help out there???

2. Hi

Try this:
=IF(\$E\$9="CRI BID DAY",D107,OFFSET(E107,0,SUBSTITUTE(\$E\$9,"Bidder #","")*2))

zeddy

3. Hey Zeddy,

I tried that and the Value sign was returned.

4. Hi

This formula assumes that in cell \$E\$9 you have something like Bidder #1, Bidder #2, .., Bidder #10
I assume there is a space after Bidder and before the #.
If there is no space, then get rid of the space in the formula, i.e. use :
=IF(\$E\$9="CRI BID DAY",D107,OFFSET(E107,0,SUBSTITUTE(\$E\$9,"Bidder#", "")*2))

zeddy

5. hmmmm tried both and the VALUE sign keeps coming back. I wish I could attach the file to this page but its to large. I would save the sheet but if I try to save it then the formula changes to value.

6. Hi

Lets try this as an example, stick this formula in a blank cell:

What this formula will do is get rid of the "Bidder #" text (by substituting a null character i.e. ""), leaving just the numeric part of "Bidder #7" i.e 7 etc.
Then, since your original 'formula' has each result spaced by 2 columns, we multipy by 2 to get the correct column number.
The formula here will just return the address of the 'lookup cell' i.e. if you have "Bidder #9" in cell [E9], this formula will return \$W\$107 as per your required 'formula'. It will return \$K\$107 if you have "Bidder #3" in cell [E9]

So try this formula first to make sure it is returning the correct cell as per your 'formula'.

zeddy

7. i feel like such a novice....well I am so the feeling is true. that returned as VALUE as well. Can I email you my file?

8. Hi

Have a look at my file attached.
(Copying and pasting formulas into a reply here sometimes gets the spacing wrong.)

Change the number in cell [E9].
I put entries in the relevant cells for Bidder #1 to Bidder #7

If you can't work it out send your file and we can 'fix' it.

zeddy

9. ## The Following User Says Thank You to zeddy For This Useful Post:

dmaynie (2012-05-07)

10. Im having trouble putting two and two together. My file seems to be a bit more confusing than yours. I get yours, but to integrate it with mine is where I get lost.

11. Hi

Is it possible for you to attach your file?
I could fix it and send it back.

zeddy

12. i tried but it is to large. and if I try to save just one worksheet it will change the formula to VALUES. If you have an email I can send it that way

13. Hi

You can send it to:
zzzwoody7@gmail.com

zeddy

14. Hi

Thanks. I've sent an updated file back to you.
The email address I gave is a temporary one which I don't check often.

By the way, I should have said "Welcome to the Lounge" for your first posting.

zeddy
zeddy

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•