vlookup specific data

[Jas5on]

Hi all, i’m ok with excel and simple vlookup but i’m not getting anywhere after looking all over the internet for hopefully a simple formula.
i’m doing a vlookup which will return specific data related to UK0006 against sheet 2 column B for station- CCTV for example. As there are multiple UK0006 choices in column b but i know i need CCTV then how do I get the return, specific text CCTV and not the others.

As i will be doing the return for 1000 plus lines with same issue, can someone help? It will be much appreciated.I've looked on the internet and various forums but can't seem to find the answer.

Column A sheet 1 contains
UK0006
UK0007
UK0008

Sheet 2
column A Column B
UK0006 Station - shop
UK0006 Station - CCTV
UK0006 Station - Car Care
UK0006 Station - Fuel
UK0007 Station - Fuel
UK0007 Station - Non Fuel
UK0007 Station - CCTV

 

[LouisT]

Do you know that UK0006 or any other values with multiple answers will always need to be CCTV?

Hi all, i’m ok with excel and simple vlookup but i’m not getting anywhere after looking all over the internet for hopefully a simple formula.
i’m doing a vlookup which will return specific data related to UK0006 against sheet 2 column B for station- CCTV for example. As there are multiple UK0006 choices in column b but i know i need CCTV then how do I get the return, specific text CCTV and not the others.

As i will be doing the return for 1000 plus lines with same issue, can someone help? It will be much appreciated.I've looked on the internet and various forums but can't seem to find the answer.

Column A sheet 1 contains
UK0006
UK0007
UK0008

Sheet 2
column A Column B
UK0006 Station - shop
UK0006 Station - CCTV
UK0006 Station - Car Care
UK0006 Station - Fuel
UK0007 Station - Fuel
UK0007 Station - Non Fuel
UK0007 Station - CCTV

 

[jtakw]

Hi,

So, are you saying, in Sheet 2, if there's "UK0006" in Column A and there's "Station - CCTV" in corresponding Column B, then return "Station - CCTV", otherwise return Nothing ?

In other words, you Only want "Station - CCTV" if it's there.

 

[Jas5on]

Yes correct. I can't seem to get a good formula as sheet 2 column A carries UK0006 but then lookup needs to choose from column B with the 4 or 5 options linked to UK0006 column A.
Most of the data for column B carries station but various options after like CCTV, Fuel etc.

 

[LouisT]

you could always swap the 2 columns in the range and search for "Station - CCTV" and return the "UK0006" instead.

 

[jtakw]

Then you don't want to use VLOOKUP, use COUNTIFS:

Change/adjust cell references/range, add sheet name, as needed:

Book1
	A	B
1	UK0006	Station - CCTV
2	UK0007	Station - CCTV
3	UK0008	No Match
4
5
6
7
8	UK0006	Station - shop
9	UK0006	Station - CCTV
10	UK0006	Station - Car Care
11	UK0006	Station - Fuel
12	UK0007	Station - Fuel
13	UK0007	Station - Non Fuel
14	UK0007	Station - CCTV
Sheet623

Cell Formulas
Range	Formula
B1	=IF(COUNTIFS(A$8:A$14,A1,B$8:B$14,"Station - CCTV"),"Station - CCTV","No Match")

 

[jtakw]

Regarding Post # 5, that won't work neither, as it'll Always return the First match of "Station - CCTV" (i.e. UK0006), regardless of the criteria for Sheet 1, Column A.

 

[Jas5on]

Call me daft please but why am I using countif. I'm literally pulling info from sheet 2column B which is linked to sheet 1A by the means of the unique number UK0006 and working on a vertical list which contains up to 6 variations of Station(always the same) but contains either, CCTV, Bakery,Shop,Car Care so if i'm looking for the data to choose which i know which is specific so could i not use lookup with "contains ("CCTV") for example as its word specific linked to UK0006 or UK0007 etc.Thanks I'm not doing any counting. Many Thanks For your help here

 

[jtakw]

Then call Me daft please, unless I'm really confused about what you're asking...

Did you even try to implement my suggestion ?

If I'm really daft and not understanding what you want, then Please show some more samples with expected results, again, have you tried my formula ?

 

[Jas5on]

i did try the formula but as i’m working over 2 sheets, i didn’t figure it out