1. ## VLOOKUP/IF (XP)

The macro I requested help for works fine.
Forgot one important feature.
CellA28 now has a drop down called either Product1, Product2 or Product3.
CellB28 has a list with products.
Somewhere else in the spreadsheet cell H28 used to be a VLOOKUP that said =VLOOKUP(B28,Products,4,FALSE)
Worked great, but now the range of products is far bigger and it has to be either column 4,9 or 14.

I've tried =IF(A28="Product1",VLOOKUP(B28,Products,4,FALSE=IF (A28="Product2",VLOOKUP(B28,Products,9,FALSE=IF(A2 8="Product3",VLOOKUP(B28,Products,14,FALSE)))))) It doesn't give a value but it says FALSE. So I must be pretty close

The products are on a sheet called Products, very original.

My brains stopped working, summer has come with a bang and its very hot...

Thanks for all the help

2. ## Re: VLOOKUP/IF (XP)

Try this:
=IF(A28="Product1",VLOOKUP(B28,Products,4,FALSE),I F(A28="Product2",VLOOKUP(B28,Products,9,FALSE),IF( A28="Product3",VLOOKUP(B28,Products,14,FALSE),"No Match")))

You cannot have = signs within your nested functions (except when it is acting as a comparison operator!) To correctly nest, you use a function as an argument of another function and you seperate them with comma's.

3. ## Re: VLOOKUP/IF (XP)

Rudi, its almost working. It works ok with Product1, but Products2 is in column 6 nd Product3 is in column 11.
The whole range is called Products

Hope this make sense
Thanks

4. ## Re: VLOOKUP/IF (XP)

Try this:
=IF(A28="Product1",VLOOKUP(B28,Products,4,FALSE),I F(A28="Product2",VLOOKUP(B28,Products,6,FALSE),IF( A28="Product3",VLOOKUP(B28,Products,11,FALSE),"No Match")))

The 3rd argument of VLookup is the column it must collect the matching value from. Change the column index number to 6 and 11 for the Products 2 and 3. See the corrected Function above!

5. ## Re: VLOOKUP/IF (XP)

Does this do what you want?

=OFFSET(INDEX(INDIRECT(A28),MATCH(B28,INDIRECT(A28 ),0)),0,3)

6. ## Re: VLOOKUP/IF (XP)

It works
Thanks
Another brilliant man
A merry Christmas to you

#### Posting Permissions

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