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
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

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
46,165
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
46,165
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
46,165
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,614
Messages
5,523,906
Members
409,542
Latest member
Shezz01

This Week's Hot Topics

Top