Search & Lookup

[marklongbottom]

I have 2 tables. Table 1 is A1:A17 and table 2 is D1:F37 (both tables will get longer at somepoint).

What I'd like to do is create a formula in cell G2 that will look at the contents of cell D2 (Table 2), then look down Table 1 and if it finds any part of the text from cell D2 in any of the cells in Table 1, copy the Table 1 cell that matches in to G2. I then need the formula to keep going down column G doing this for all the data.

The contents of the cells in column D could have the phrase in any part of the cells in column A.

Can anyone help?

 

[T. Valko]

I have 2 tables. Table 1 is A1:A17 and table 2 is D1:F37 (both tables will get longer at somepoint).

What I'd like to do is create a formula in cell G2 that will look at the contents of cell D2 (Table 2), then look down Table 1 and if it finds any part of the text from cell D2 in any of the cells in Table 1, copy the Table 1 cell that matches in to G2. I then need the formula to keep going down column G doing this for all the data.

The contents of the cells in column D could have the phrase in any part of the cells in column A.

Can anyone help?

Can you post a few rows worth of data and tell/show us what results you expect?

 

[marklongbottom]

Unfortunately Mr Excel doesn't allow me to put a screen shot on here. I'll have to retype it out.

 

[marklongbottom]

I have tried my best to label the columns but without borders it is a bit hard to read. The 3 columns at the end are my formula columns and the headers e.g. Matches Column A & D, etc show my expected results.


Table 1 (column A) Table 2 (column D) Table 2 (column E) Table 2 (column F) Matches Col A & D Matches Col A & E Matches Col A & F
Abu Dabbab Not divulged Not divulged Not divulged
Agi Dagi Unknown - EA03339, Black Angel SARL Black Angel
Aktogay RMG Auburn RMG Chilisai Chilisai
Alaigyr Bobrikovo Minerals Minerals Minerals Bobrikovo
Altintepe Minex Chaarat Not known Not known Chaarat
Altyntas Aalborg Portland Andash Shell lining Andash
Andash Al Alaigyr 31T - Phosphate Factory Alaigyr
Benkala Qaim Al Qaim Phosphate Factory
Biely Vrch Bobrikovo Minerals Minerals Minerals Bobrikovo
Bir El Afou EP Rossing Aktogay Ura Eronga Region Rossing Uranium Aktogay
Black Angel - - AO11-0021 Uitbreiding LTRB
Bobrikovo Unknown - EA03339, Black Angel SARL Black Angel
Bozschakol Bir El Afou EP Mine - - Bir El Afou EP
Chaarat Northland Resourses Kaunisvaara Kaunisvaara Iron Ore Study
Chilisai Kazakhmys Bozshakol Bozshakol Copper Project Bozschakol Bozschakol

 

[marklongbottom]

This didn't work at all. Will have to find another website to help.

 

[T. Valko]

This didn't work at all. Will have to find another website to help.

There is an add-in at this link that allows you to post sample data from an Excel file in a nice grid format.

http://www.excel-jeanie-html.de/index.php?f=1

 

[marklongbottom]

Excel 2010
	A	B	C	D	E	F	G	H	I
1	Table 1 (column A)			Table 2 (column D)
2	Table 1 (column A)			Table 2 (column D)	Table 2 (column E)	Table 2 (column F)	Matches Column A & D	Matches Column A & E	Matches Column A & F
3	Abu Dabbab			Not divulged	Not divulged	Not divulged
4	Agi Dagi			Unknown	-	EA03339, Black Angel SARL			Black Angel
5	Aktogay			RMG	Auburn	RMG Chilisai			Chilisai
6	Alaigyr			Bobrikovo Minerals	Minerals	Minerals	Bobrikovo
7	Altintepe			Minex Chaarat	Not known	Not known	Chaarat
8	Altyntas			Aalborg Portland	Andash	Shell lining for sand mill		Andash
9	Andash			Al Alaigyr 31T	-	Phosphate Factory	Alaigyr
10	Benkala			Qaim	Al Qaim	Phosphate Factory
11	Biely Vrch			Bobrikovo Minerals	Minerals	Minerals	Bobrikovo
12	Bir El Afou EP			Rossing Aktogay Uranium	Eronga Region - Desert	Rossing Uranium	Aktogay
13	Black Angel			-	-	AO11-0021 Uitbreiding LTRB
14	Bobrikovo			Unknown	-	EA03339, Black Angel SARL			Black Angel
15	Bozschakol			Bir El Afou EP Mine	-	-	Bir El Afou EP
16	Chaarat			Northland Resourses AB	Kaunisvaara	Kaunisvaara Iron Ore Study
17	Chilisai			Kazakhmys	Bozshakol	Bozshakol Copper Project		Bozschakol	Bozschakol
Sheet1

Cell Formulas
Range	Formula
A1	Table 1 (column A)
A2	Table 1 (column A)
A3	Abu Dabbab
A4	Agi Dagi
A5	Aktogay
A6	Alaigyr
A7	Altintepe
A8	Altyntas
A9	Andash
A10	Benkala
A11	Biely Vrch
A12	Bir El Afou EP
A13	Black Angel
A14	Bobrikovo
A15	Bozschakol
A16	Chaarat
A17	Chilisai
D1	Table 2 (column D)
D2	Table 2 (column D)
D3	Not divulged
D4	Unknown
D5	RMG
D6	Bobrikovo Minerals
D7	Minex Chaarat
D8	Aalborg Portland
D9	Al Alaigyr 31T
D10	Qaim
D11	Bobrikovo Minerals
D12	Rossing Aktogay Uranium
D13	-
D14	Unknown
D15	Bir El Afou EP Mine
D16	Northland Resourses AB
D17	Kazakhmys
E2	Table 2 (column E)
E3	Not divulged
E4	-
E5	Auburn
E6	Minerals
E7	Not known
E8	Andash
E9	-
E10	Al Qaim
E11	Minerals
E12	Eronga Region - Desert
E13	-
E14	-
E15	-
E16	Kaunisvaara
E17	Bozshakol
F2	Table 2 (column F)
F3	Not divulged
F4	EA03339, Black Angel SARL
F5	RMG Chilisai
F6	Minerals
F7	Not known
F8	Shell lining for sand mill
F9	Phosphate Factory
F10	Phosphate Factory
F11	Minerals
F12	Rossing Uranium
F13	AO11-0021 Uitbreiding LTRB
F14	EA03339, Black Angel SARL
F15	-
F16	Kaunisvaara Iron Ore Study
F17	Bozshakol Copper Project
G2	Matches Column A & D
G6	Bobrikovo
G7	Chaarat
G9	Alaigyr
G11	Bobrikovo
G12	Aktogay
G15	Bir El Afou EP
H2	Matches Column A & E
H8	Andash
H17	Bozschakol
I2	Matches Column A & F
I4	Black Angel
I5	Chilisai
I14	Black Angel
I17	Bozschakol

 

[T. Valko]

Excel 2010

	A	B	C	D	E	F	G	H	I
1	Table 1 (column A)			Table 2 (column D)
2	Table 1 (column A)			Table 2 (column D)	Table 2 (column E)	Table 2 (column F)	Matches Column A & D	Matches Column A & E	Matches Column A & F
3	Abu Dabbab			Not divulged	Not divulged	Not divulged
4	Agi Dagi			Unknown	-	EA03339, Black Angel SARL			Black Angel
5	Aktogay			RMG	Auburn	RMG Chilisai			Chilisai
6	Alaigyr			Bobrikovo Minerals	Minerals	Minerals	Bobrikovo
7	Altintepe			Minex Chaarat	Not known	Not known	Chaarat
8	Altyntas			Aalborg Portland	Andash	Shell lining for sand mill		Andash
9	Andash			Al Alaigyr 31T	-	Phosphate Factory	Alaigyr
10	Benkala			Qaim	Al Qaim	Phosphate Factory
11	Biely Vrch			Bobrikovo Minerals	Minerals	Minerals	Bobrikovo
12	Bir El Afou EP			Rossing Aktogay Uranium	Eronga Region - Desert	Rossing Uranium	Aktogay
13	Black Angel			-	-	AO11-0021 Uitbreiding LTRB
14	Bobrikovo			Unknown	-	EA03339, Black Angel SARL			Black Angel
15	Bozschakol			Bir El Afou EP Mine	-	-	Bir El Afou EP
16	Chaarat			Northland Resourses AB	Kaunisvaara	Kaunisvaara Iron Ore Study
17	Chilisai			Kazakhmys	Bozshakol	Bozshakol Copper Project		Bozschakol	Bozschakol

<COLGROUP><COL style="BACKGROUND-COLOR: #dae7f5"><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>

Sheet1​

Worksheet Formulas Cell Formula A1 Table 1 (column A) B1 C1 D1 Table 2 (column D) E1 F1 G1 H1 I1 A2 Table 1 (column A) B2 C2 D2 Table 2 (column D) E2 Table 2 (column E) F2 Table 2 (column F) G2 Matches Column A & D H2 Matches Column A & E I2 Matches Column A & F A3 Abu Dabbab B3 C3 D3 Not divulged E3 Not divulged F3 Not divulged G3 H3 I3 A4 Agi Dagi B4 C4 D4 Unknown E4 - F4 EA03339, Black Angel SARL G4 H4 I4 Black Angel A5 Aktogay B5 C5 D5 RMG E5 Auburn F5 RMG Chilisai G5 H5 I5 Chilisai A6 Alaigyr B6 C6 D6 Bobrikovo Minerals E6 Minerals F6 Minerals G6 Bobrikovo H6 I6 A7 Altintepe B7 C7 D7 Minex Chaarat E7 Not known F7 Not known G7 Chaarat H7 I7 A8 Altyntas B8 C8 D8 Aalborg Portland E8 Andash F8 Shell lining for sand mill G8 H8 Andash I8 A9 Andash B9 C9 D9 Al Alaigyr 31T E9 - F9 Phosphate Factory G9 Alaigyr H9 I9 A10 Benkala B10 C10 D10 Qaim E10 Al Qaim F10 Phosphate Factory G10 H10 I10 A11 Biely Vrch B11 C11 D11 Bobrikovo Minerals E11 Minerals F11 Minerals G11 Bobrikovo H11 I11 A12 Bir El Afou EP B12 C12 D12 Rossing Aktogay Uranium E12 Eronga Region - Desert F12 Rossing Uranium G12 Aktogay H12 I12 A13 Black Angel B13 C13 D13 - E13 - F13 AO11-0021 Uitbreiding LTRB G13 H13 I13 A14 Bobrikovo B14 C14 D14 Unknown E14 - F14 EA03339, Black Angel SARL G14 H14 I14 Black Angel A15 Bozschakol B15 C15 D15 Bir El Afou EP Mine E15 - F15 - G15 Bir El Afou EP H15 I15 A16 Chaarat B16 C16 D16 Northland Resourses AB E16 Kaunisvaara F16 Kaunisvaara Iron Ore Study G16 H16 I16 A17 Chilisai B17 C17 D17 Kazakhmys E17 Bozshakol F17 Bozshakol Copper Project G17 H17 Bozschakol I17 Bozschakol <THEAD> </THEAD><TBODY> </TBODY>

<TBODY>
</TBODY>

Sorry, I don't understand.

 

[marklongbottom]

Sorry you will have to excuse the bottom formula table. I don't know where that came from. It is the top table.
I'll try to explain again based on the table at the top.
The orange columns are the columns (G, H and I) I want my formulas in. G6 shows you what I want to show. This would be based on the formula looking at D6 then looking down the information in all the cells in column A to find any part of the contents of column D. In this case the formula would look for Bobrikovo and/or Minerals in column A but as only Bobrikovo is present it would only return Bobrikovo. Column H would need to do the same for column E and column A. And column I would need to do the same but for column F and column A.
Is this possible?

 

[T. Valko]

Sorry you will have to excuse the bottom formula table. I don't know where that came from. It is the top table.
I'll try to explain again based on the table at the top.
The orange columns are the columns (G, H and I) I want my formulas in. G6 shows you what I want to show. This would be based on the formula looking at D6 then looking down the information in all the cells in column A to find any part of the contents of column D. In this case the formula would look for Bobrikovo and/or Minerals in column A but as only Bobrikovo is present it would only return Bobrikovo. Column H would need to do the same for column E and column A. And column I would need to do the same but for column F and column A.
Is this possible?

This is not possible using formulas.

You would need a VBA procedure to do this. Unfortunately, I'm not much of a programmer so someone else will need to help you with this.

Good luck!