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

1. ## 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!  Register To Reply

2. ## 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)  Register To Reply

3. ## 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  Register To Reply

4. ## 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?  Register To Reply

5. ## 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.  Register To Reply

6. ## 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  Register To Reply

7. ## 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?  Register To Reply

8. ## 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?  Register To Reply

9. ## 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   Register To Reply

10. ## 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?  Register To Reply