Help with the IF function or anything that can help

jakesnip

New Member
Joined
Oct 8, 2013
Messages
3
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.

please help :)
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

bschwartz

Well-known Member
Joined
Jan 5, 2013
Messages
1,367
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
Joined
Oct 8, 2013
Messages
3
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
Joined
Jan 5, 2013
Messages
1,367
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
Joined
Oct 8, 2013
Messages
3
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
Joined
Jan 5, 2013
Messages
1,367
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

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet27



Pants and Orange, you would get 222:

ABC
1GarmentsColorLOOKUP VALUE
2PantsOrange222

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</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)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,940
Messages
5,598,975
Members
414,270
Latest member
skipolmsted

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?

Disable AdBlock

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
Top