Help with the IF function or anything that can help

[jakesnip]

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

 

[bschwartz]

Is this what you are looking for?

Drop down lists (formula in C2):

	A	B	C
TABLE	SPECIFIC	LOOKUP VALUE
TABLE 1	VERY SPECIFIC

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]555[/TD]

</tbody>

Lookup Table (worksheet name LOOKUP):

	A	B	C
TABLE	SPECIFIC	VALUE
TABLE 1	VERY SPECIFIC
TABLE 2	LESS SPECIFIC

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]555[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]444[/TD]

</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]

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

[TABLE="class: cms_table"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="align: center"][/TH]
[TH="align: center"] A [/TH]
[TH="align: center"] B[/TH]
[TH="align: center"] C [/TH]
[/TR]
</tbody>[/TABLE]
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]

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]

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]

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:

	A	B	C
Color	Style	Value
Shirts	Green
Pants	Yellow
Pants	Blue
Shirts	Orange
Jeans	Purple
Pants	Orange
Jeans	Green
Shirts	Blue
Shirts	Yellow

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]555[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]444[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]888[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]777[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]333[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]222[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]111[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]666[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]999[/TD]

</tbody>

Data​

If you entered Shirts and Green, you would get 555:

	A	B	C
Garments	Color	LOOKUP VALUE
Shirts	Green

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]555[/TD]

</tbody>

Sheet27​

Pants and Orange, you would get 222:

	A	B	C
Garments	Color	LOOKUP VALUE
Pants	Orange

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]222[/TD]

</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)