Returning Text in adjacent column

[Jim411]

In a separate worksheet, using a formula, I want to find certain text in column A (in Sheet1) plus other text in column B (same row) and get the text from column C in the same row. Example: looking for, lets say, "USA" in column A and "Other Lakes" in column B and return the name of a lake from column C, "Lake Sinclair". USA will appear many times and different lakes will appear, the idea is to get a list of the different lakes in a column in a sheet other than Sheet1. The eventual plan is to create a Combo box from this list. I want it in a separate sheet because I want to be able to update Sheet1. There would probably be no more than 200 rows.

Thanks,
Jim

 

[cyrilbrd]

Hi,
In order to post a small smaple of your dat kindly use one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste

 

[WarPigl3t]

I'm sure you already know about the VLOOKUP funcion. Well you can use the same formula to lookup multiple criteria, not just one criteria. To do that, you must turn in into an array.
=VLOOKUP(A1:B1,A2:C10,3,FALSE)
Control Shift Enter
not just Enter
A1 and B1 is your lookup values
A1 = "USA
B1 = "Other Lakes"

 

[Peter_SSs]

Welcome to the MrExcel board!

... the idea is to get a list of the different lakes in a column in a sheet other than Sheet1.

Suppose your first formula in the other sheet is in cell A2

=IFERROR(INDEX(Sheet1!C$2:C$200,SMALL(IF(Sheet1!A$2:A$200="USA",IF(Sheet1!B$2:B$200="Other Lakes",ROW(Sheet1!A$2:A$200)-ROW(Sheet1!A$2)+1)),ROWS(A$2:A2))),"")

If that first formula is not in cell A2, then change the blue cell references to whatever the first formula cell is (but keep the $ sign)

This is an array formula so should be confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert {} around the formula. The formula can then be copied down.

 

[ztodd]

I think it can be a little simpler -

=INDEX(C:C,SUM(IF(("USA"=A$1:A$10)*("Other Lakes"=B$1:B$10)=1,ROW(C$1:C$10),0)))

It's also an array formula.

If you don't want to use an array formula, insert a "helper" column to concatenate A & B together, then you can just do a simple vlookup using that.

 

[Peter_SSs]

A few comments

I think it can be a little simpler -

=INDEX(C:C,SUM(IF(("USA"=A$1:A$10)*("Other Lakes"=B$1:B$10)=1,ROW(C$1:C$10),0)))

It's also an array formula.

1. This doesn't reference a different worksheet as the OP requested.

2. I don't know just what data the OP has but with my sample data this just returns zero. Can you post your sample data?

3. The OP wants to create a list of all the different lakes that meet the criteria in columns A & B. It seems to me that this formula, if copied down, would keep referencing the same cell in column C.

4. Strange that you reference the whole of column C in your INDEX, but only check 10 rows for the criteria to match.

If you don't want to use an array formula, insert a "helper" column to concatenate A & B together, then you can just do a simple vlookup using that.

5. As already mentioned, the OP wants to create a list of all lakes meeting the criteria. Won't a "simple vlookup" only return the first lake that meets the ctiteria?

 

[ztodd]

Oh I guess that's probably what he meant. Good job understanding his post - I didn't get that part.

 

[Jim411]

Welcome to the MrExcel board!

Suppose your first formula in the other sheet is in cell A2

=IFERROR(INDEX(Sheet1!C$2:C$200,SMALL(IF(Sheet1!A$2:A$200="USA",IF(Sheet1!B$2:B$200="Other Lakes",ROW(Sheet1!A$2:A$200)-ROW(Sheet1!A$2)+1)),ROWS(A$2:A2))),"")

If that first formula is not in cell A2, then change the blue cell references to whatever the first formula cell is (but keep the $ sign)

This is an array formula so should be confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert {} around the formula. The formula can then be copied down.

____________________________

If I put in this formula (edited with actual column letters/text) and do Ctrl+Shift+Enter nothhing appears, although if I just do click Enter the first label in the Sheet 1 (L2) shows up but nothing else when I copy it down...

=IFERROR(INDEX(Sheet1!L$2:L$200,SMALL(IF(Sheet1!I$2:I$200="US",IF(Sheet1!K$2:K$200="Yes, please specify the index",ROW(Sheet1!A$2:A$200)-ROW(Sheet1!A$2)+1)),ROWS(A$2:A2))),"")

...any suggestions.

Thanks (for all the replies)
Jim

 

[Peter_SSs]

...any suggestions.

Yes, show us a small set of sample data so we can be sure we are dealing with the correct data and layout. My signature block below has suggestions for showing small screen shots (like I have here) and where to test them. You can substitute any sensitive data.

Here's my sample data, with your adjusted formula. From what I understand, this is producing the results you want. If not please give more detail as well as some sample data and expected results.

Excel Workbook
	I	J	K	L
1
2	AU		Yes, please specify the index	Item 1
3	US		Yes, please specify the index	Item 2
4	US		No	Item 3
5	CN		Yes, please specify the index	Item 4
6	US		Yes, please specify the index	Item 5
7
Sheet1

Excel Workbook
	A
1
2	Item 2
3	Item 5
4
5
Sheet2

 

[Jim411]

Yes, show us a small set of sample data so we can be sure we are dealing with the correct data and layout. My signature block below has suggestions for showing small screen shots (like I have here) and where to test them. You can substitute any sensitive data.

Here's my sample data, with your adjusted formula. From what I understand, this is producing the results you want. If not please give more detail as well as some sample data and expected results.

Sheet1

*	I	J	K	L
*	*	*	*
AU	*	Yes, please specify the index	Item 1
US	*	Yes, please specify the index	Item 2
US	*	No	Item 3
CN	*	Yes, please specify the index	Item 4
US	*	Yes, please specify the index	Item 5
*	*	*	*

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 56px;"><col style="width: 24px;"><col style="width: 208px;"><col style="width: 72px;"></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]

[TD="bgcolor: #CACACA, align: center"]6[/TD]

[TD="bgcolor: #CACACA, align: center"]7[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


Sheet2

*	A
*
Item 2
Item 5
*
*

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 101px;"></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula A2 {=IFERROR(INDEX(Sheet1!L$2:L$200,SMALL(IF(Sheet1!I$2:I$200="US",IF(Sheet1!K$2:K$200="Yes, please specify the index",ROW(Sheet1!A$2:A$200)-ROW(Sheet1!A$2)+1)),ROWS(A$2:A2))),"")} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

---------------------------
Thanks, thats working, turns out there was a blank space after the word Index, sorry about that (I guess I was copying and pasting the formula too many times). One more thing if possible, there are some cases where the participant didn't fill in anything and it's leaving blank spaces, can that be fixed?

Either way, thanks for all your help.

Jim