+ Reply to Thread
Results 1 to 10 of 10

VLOOKUP Formula Problem - Comparing variables in data table and outputting in a cell?

  1. #1
    Registered User
    Join Date
    04-25-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    91

    VLOOKUP Formula Problem - Comparing variables in data table and outputting in a cell?

    Hi Guys,

    I would be really grateful for someone’s help to work out what the VLOOKUP formula should be in the highlighted yellow cell (F16), the highlighted blue cell (F17) and the highlighted green cell (F18). I attach the worksheet.

    Let me explain how this is meant to work.

    1) In Box A, you first MUST select one of two variables “100/200” or “200/400”. You will see that when you click one of these buttons, the data name is reproduced in cell J2 (and this matches the data in column A$21:A$26, depending on whether you select “100/200” or “200/400”).

    2) There are then four box “Bs”, as you can see, and each box has two variables within them. You will see that pressing a combination of buttons reveals a corresponding “code” in cell K2. This could be 1112,1111, etc, depending on the buttons you push, as each button in each of the four boxes is allocated a value which is either “1” or “2”.

    For now, I only am concerned with the combination 1111, which is output in K$2 if you push the buttons labelled “Big” in each of the four “Box Bs”.

    - You will then see that the combination 1111 is also contained within the data table at B$21:B$26.

    3) What I want to happen is this:

    EITHER
    a. I click on 100/200 in Box A, and then “Big, Big, Big, Big” in Boxes B.
    b. I would then like for the data in D$21 to be reproduced in F$16 AND the data in D$22 to be reproduced in F$17 AND the data in D$23 to be reproduced in F$18.

    OR,

    a. I click on 200/400 in Box A, and then “Big, Big, Big, Big” in Boxes B.
    b. I would then like for the data in D$24 to be reproduced in F$16, the data in D$25 to be reproduced in F$17 and the data in D$26 to be reproduced in F$18.

    I hope this makes sense, but I would be REALLY grateful for someone’s help!
    Attached Files Attached Files

  2. #2
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: VLOOKUP Formula Problem - Comparing variables in data table and outputting in a cell?

    Use the following..in F16 to F18 respectively
    =INDEX($D$21:$D$26,MATCH(1,INDEX(($A$21:$A$26=$J$2)*($B$21:$B$26=$K$2),0,1),0))
    =INDEX($D$21:$D$26,MATCH(1,INDEX(($A$21:$A$26=$J$2)*($B$21:$B$26=$K$2),0,1),0)+1)
    =INDEX($D$21:$D$26,MATCH(1,INDEX(($A$21:$A$26=$J$2)*($B$21:$B$26=$K$2),0,1),0)+2)
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Registered User
    Join Date
    04-25-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    91

    Re: VLOOKUP Formula Problem - Comparing variables in data table and outputting in a cell?

    I cannot thank you enough. Seriously - I haven been trying to figure this out for a couple of days now - and you have totally nailed it.

    Thank you so much.

    Nick

  4. #4
    Registered User
    Join Date
    04-25-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    91

    Re: VLOOKUP Formula Problem - Comparing variables in data table and outputting in a cell?

    One final question - and this, I hope is far easier than the last one!

    For example, if I were to make BOLD the text in cell D$21 (or change the colour of the text etc in D$21), how could I reproduce this formatting (bold or colour etc) in cell F$16?

  5. #5
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: VLOOKUP Formula Problem - Comparing variables in data table and outputting in a cell?

    You could simply copy--paste special --formats.

    Alternatively (and dynamically speaking), if you want format of cells F16-F18 to change based on your selection in the boxes, you could look at conditional formatting for those cells.

  6. #6
    Registered User
    Join Date
    04-25-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    91

    Re: VLOOKUP Formula Problem - Comparing variables in data table and outputting in a cell?

    Hey - Ace - thanks for your input. I am very new to Excel and am reading as much as possible - but I am not sure how conditional formatting would help?

    As the spreadsheet is, when you select a combination of buttons, it searches for the matching data and then reproduces it in cells F$16, F$17 and
    F$18.

    The above formula works perfectly for that.

    But if I were to (to make it simple), make ALL of the data table bold and in red text, I still don't follow how that formatting can be recreated in cells
    F$16, F$17 and F$18, which contain the relevant formulas?

    Thanks so much for your help.

    Nick

  7. #7
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: VLOOKUP Formula Problem - Comparing variables in data table and outputting in a cell?

    Why not format the cells (bold, red text etc.) F16-F18 directly? As I see it, the table is a reference to pull up values that you need and not you final output

    Wouldn't this be easier?

  8. #8
    Registered User
    Join Date
    04-25-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    91

    Re: VLOOKUP Formula Problem - Comparing variables in data table and outputting in a cell?

    Hey Ace,

    Yes - that works, but let me be more specific if I may.

    If the data cell contains the phrase "Hello and Hi" and I only want the word "Hello" to be in bold and in red and the words "and Hi" to be in italics and blue, how would I be able to reproduce that formatting?

  9. #9
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: VLOOKUP Formula Problem - Comparing variables in data table and outputting in a cell?

    Unfortunately, you cant do partial formatting with cells containing formulas, only ones containing values

  10. #10
    Registered User
    Join Date
    04-25-2012
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    91

    Re: VLOOKUP Formula Problem - Comparing variables in data table and outputting in a cell?

    Ahh, I see! Thanks for that - that is annoying! Is there the possibility of creating some kind of VBA script to do that?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1