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!

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

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?

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

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?

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