Use a Vlookup only when a separate column contains a certain text.

[chewybar1986]

Hi All,

I'm trying to create a vlookup that only searches through rows where a separate column (not part of the vlookup formula) contains the word "CHG-529C" and the vlookup only uses those rows that contain that particular word.

So Column A is where CHG-529C will be which is completely separate from the vlookup. so I was thinking maybe some sort of IF function might work.

=IF($A:$A="chg-529c",(VLOOKUP(B2,$C:$N,12,FALSE)))

But this doesn't seem to work... It seems the problem is the fact I'm looking through a whole column and not just a singular Cell.... Any ideas??

Thanks in advance

 

[hagia_sofia]

Hello, could you please answer these two questions: a) what do you mean by "contains": "is equal" or "contains"? b) what version of Excel are you using?

 

[chewybar1986]

Hello, could you please answer these two questions: a) what do you mean by "contains": "is equal" or "contains"? b) what version of Excel are you using?

Hi, A) is equal
B) 2016

Thanks

 

[hagia_sofia]

Would this work then:

=INDEX(N:N,MATCH(1,(A:A="CHG-529C")*(C:C=B2),0),0)

 

[chewybar1986]

Would this work then:

=INDEX(N:N,MATCH(1,(A:A="CHG-529C")*(C:C=B2),0),0)

Unfortunately no, it just gives me an #N/A

 

[hagia_sofia]

Unfortunately no, it just gives me an #N/A

Even when hitting Ctrl+Shift+Enter after copying/typing it?

 

[chewybar1986]

Unfortunately no, it just gives me an #N/A

Just to try and make it clearer i'm trying to get the correct Design status and the vlookup doesnt work because its looking at through column B to M and say the cell its trying to match is 2ASH00199-0002 it will give me the first match it finds so the formula will give me an answer as "Working" but i want the vlookup to only search through the rows that Column A have as CHG-529C.

So in this example the end goal would be to get the formula to turn up if the search was (2ASH00199-0001 is Released FML) (2ASH00199-0002 is Released FML) (2ASH00199-0003 is Released FML)

Change Request

Package Number

Nomenclature

Change Order #

Type Version & Start Unit

Lot

Method of Embodiment

Part Type

Internal Design Freeze Date

Target Release Date

Internal SOP Need Date

Customer Need Date

Design Status

CR-032432-PO-NEW

2ASH00199-0001

Heat Shield

CO-032432-0232

CV

V5-CHG1774

Assy

45159​

45173​

45187​

45199​

Working

CHG-529C

2ASH00199-0001

Heat Shield

CO-041087-0016

Aft-CHG7035

Aft

Att + PM (or other Drawing Face Change)

Assy

45835​

45849​

45863​

45877​

Released FML

CHG-529B

2ASH00199-0001

Heat Shield

CO-032432-0232

AFT-CHG8864

CV

ACB + 242 CATIA

Assy

45364​

45378​

45394​

45408​

Planning

CR-032432-PO-NEW

2ASH00199-0002

Heat Shield

CO-032432-0232

CV

V5-CHG1774

Assy

45159​

45173​

45187​

45199​

Working

CHG-529C

2ASH00199-0002

Heat Shield

CO-041087-0016

Aft-CHG7035

Aft

Att + PM (or other Drawing Face Change)

Assy

45835​

45849​

45863​

45877​

Released FML

CHG-529B

2ASH00199-0002

Heat Shield

CO-032432-0232

AFT-CHG8864

CV

ACB + 242 CATIA

Assy

45364​

45378​

45394​

45408​

Planning

CR-032432-PO-NEW

2ASH00199-0003

Heat Shield

CO-032432-0232

CV

V5-CHG1774

Assy

45159​

45173​

45187​

45199​

Working

CHG-529C

2ASH00199-0003

Heat Shield

CO-041087-0016

Aft-CHG7035

Aft

Att + PM (or other Drawing Face Change)

Assy

45835​

45849​

45863​

45877​

Released FML

CHG-529B

2ASH00199-0003

Heat Shield

CO-032432-0232

AFT-CHG8864

CV

ACB + 242 CATIA

Assy

45364​

45378​

45394​

45408​

Planning

 

[chewybar1986]

Even when hitting Ctrl+Shift+Enter after copying/typing it?

actually this might have worked, i just forgot to make it an array lol