# Returning Text in adjacent column

#### Jim411

##### New Member
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

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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"

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.

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.

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?

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

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...

...any suggestions.

Thanks (for all the replies)
Jim

...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
IJKL
1
2AUYes, please specify the indexItem 1
3USYes, please specify the indexItem 2
4USNoItem 3
5CNYes, please specify the indexItem 4
6USYes, please specify the indexItem 5
7
Sheet1

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

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 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 * * * *

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

Excel tables to the web >> Excel Jeanie HTML 4

Sheet2

 * A 1 * 2 Item 2 3 Item 5 4 * 5 *

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 101px;"></colgroup><tbody>
</tbody>

 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

Replies
2
Views
311
Replies
1
Views
282
Replies
6
Views
204
Replies
2
Views
142
Replies
1
Views
243

1,220,987
Messages
6,157,238
Members
451,407
Latest member
vdaesety

### 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.

### Which adblocker are you using?

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

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