# Help with the IF function or anything that can help

#### jakesnip

##### New Member
I am trying to set up a spreadsheet for work and am having a bit of trouble.

I have created a drop down list, well two actually. These are in A2 & B2. From here I want cell C2 to display something from another worksheet depending on what it says in these two columns.

The other worksheet will have several tables in it. A2 displays the name of the tables, B2 then displays the specifics of it.

So to rephrase I want C2 to display a number from sheet 2 after looking up what cell's A2 and B2 display. If the dropdown is then changed though I want it to be able to recognize this and change it to the appropriate cell. I think I know how to do this with one cell but referring to two cells just confuses me.

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

#### bschwartz

##### Well-known Member
Is this what you are looking for?

Drop down lists (formula in C2):
ABC
1TABLESPECIFICLOOKUP VALUE
2TABLE 1VERY SPECIFIC555

<tbody>
</tbody>

Lookup Table (worksheet name LOOKUP):
ABC
1TABLESPECIFICVALUE
2TABLE 1VERY SPECIFIC555
3TABLE 2LESS SPECIFIC444

<tbody>
</tbody>
LOOKUP

This formula in C2. Confirm with Ctrl+Shift+Enter, instead of just Enter:
=INDEX(LOOKUP!C2:C999,SMALL(IF(A2=LOOKUP!A2:A999,IF(LOOKUP!B2:B3=B2,ROW(LOOKUP!C2:C999)-ROW(LOOKUP!C2)+1)),ROWS(Sheet16!A\$2:A2)))

#### jakesnip

##### New Member
I don't think so. I have Lists already made and being used as drop downs already.

A B C

<tbody>
</tbody>
1- Dropdown1- Dropdown 2- If A1=Shirts and B1=Green display cell B4 from 'data' sheet.

Sorry I am a little inexperienced with excel. So basically if someone clicks on the drop down they have all the options you can choose and if they select this it changes appropriately. Obviously this will be a very long formula but if you can help me with just one option of it I can fill out the rest!

When I said tables I meant just using cells as a table. Not actually formatting a table. ie using borders etc to make it look like a table

Update. Sorry for column formatting above. No clue how to do it properly

#### bschwartz

##### Well-known Member
Can you describe the table that is on your worksheet called 'Data'?

In your example above, you want to look up Shirts and Green in which columns of the sheet 'Data' in order to return the contents of the B column?

Post a real example using sample data.

#### jakesnip

##### New Member
This is the 'table' that will be referred to. The 1-20 numbers are just for reference for me. Not relevant to anything else. 2013-10-09_1452 - jakesnip's library

This is the dropdown example of what I mean. A1 and B1 are both dropdowns. Cell C1 is where I want to display the formula that will say if A1 and B1 are certain things then display cell 'x'. 2013-10-09_1459 - jakesnip's library

I thought it would be something like
=if(A1="shirts"&B1="green",Data!B2,0)

But then of course repeating this necessary amount of times for each option (long job) haha

#### bschwartz

##### Well-known Member
Jakesnip,

In order for Shirts and Green to return a value, they would need to be in the lookup table. For example, if you had this data you could use a formula to extract the value from column C (say an item code) based on a dropdown of columns A and B:

ABC
1ColorStyleValue
2ShirtsGreen555
3PantsYellow444
4PantsBlue888
5ShirtsOrange777
6JeansPurple333
7PantsOrange222
8JeansGreen111
9ShirtsBlue666
10ShirtsYellow999

<tbody>
</tbody>
Data
If you entered Shirts and Green, you would get 555:

ABC
1GarmentsColorLOOKUP VALUE
2ShirtsGreen555

</tbody>
Sheet27

Pants and Orange, you would get 222:

ABC
1GarmentsColorLOOKUP VALUE
2PantsOrange222

</tbody>
Sheet27

In your specific example, you would need to lookup based on the unique value in column A (1, 2, etc). If you entered that, you could lookup a value in column B.

That would be something like this, if you entered the unique number in cell A2:

=VLOOKUP(a2,Data!A2:A21,2,0)

Replies
1
Views
71
Replies
3
Views
51
Replies
3
Views
138
Replies
2
Views
99
Replies
14
Views
249

1,128,205
Messages
5,629,281
Members
416,383
Latest member
Joe Reiter

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back