Returning Text in adjacent column

Jim411

New Member
Joined
Jan 26, 2012
Messages
7
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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

WarPigl3t

Well-known Member
Joined
May 25, 2014
Messages
1,609
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,272
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Sep 17, 2014
Messages
221

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,272
Office Version
  1. 365
Platform
  1. Windows
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?
 
Last edited:

ztodd

Board Regular
Joined
Sep 17, 2014
Messages
221

ADVERTISEMENT

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

Jim411

New Member
Joined
Jan 26, 2012
Messages
7
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,272
Office Version
  1. 365
Platform
  1. Windows
...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
 

Jim411

New Member
Joined
Jan 26, 2012
Messages
7
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

*IJKL
1****
2AU*Yes, please specify the indexItem 1
3US*Yes, please specify the indexItem 2
4US*NoItem 3
5CN*Yes, please specify the indexItem 4
6US*Yes, please specify the indexItem 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*
2Item 2
3Item 5
4*
5*

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

Spreadsheet Formulas
CellFormula
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,598
Messages
5,832,646
Members
430,150
Latest member
amitk1

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