Multiple Scenario VLookup

[odonovanc]

What is the formula if I want to do a vlookup and there are multiple scenarios.

I want to lookup say A1 in sheet 2 range A1:F100. If there is an X in column 2 I want to return the value "Text". OR If there is an X in column 3 then I want it to return "Different Text", and if neither column has an X I want it to remain blank "". It will never happen that there is an X in both. Can someone help me write this formula? Thanks.

 

[Fluff]

How about
=IF(COUNTIFS(sheet2!A:A,A2,sheet2!B:B,"X")>0,"Text",IF(COUNTIFS(sheet2!A:A,A2,sheet2!C:C,"X")>0,"Different text",""))

 

[odonovanc]

How about
=IF(COUNTIFS(sheet2!A:A,A2,sheet2!B:B,"X")>0,"Text",IF(COUNTIFS(sheet2!A:A,A2,sheet2!C:C,"X")>0,"Different text",""))

Don't think I was clear. I need to lookup cell A1 in sheet 1 in sheet 2 Range A1:F100. If there is an X in column 2 of sheet 2 I want to return the value "Text". OR If there is an X in column 3 of sheet 2 then I want it to return "Different Text", and if neither column has an X I want it to remain blank "". It will never happen that there is an X in both. Thanks.

 

[odonovanc]

Let's say cell A1 in sheet 1 is a city name. Chicago. I need to lookup that city in sheet 2 and write "Text" or "different Text" or leave blank based on whether one of those columns after the city column (Column A in sheet 2) have an X or if they are blank. I hope that is more clear.

 

[Toadstool]

Hi Odonovanc,

Try
=IF(ISNA(VLOOKUP("X",Sheet2!$B$1:$B$100,1,0)),IF(ISNA(VLOOKUP("X",Sheet2!$C$1:$C$100,1,0)),"","Different text"),"Text")

 

[Fluff]

That's what my formula does.

 

[Fluff]

@Toadstool
That is not taking into account col A matching.

 

[Toadstool]

@Toadstool
That is not taking into account col A matching.

OP didn't mention column A in sheet2, just a check of columns 2 and 3.

 

[Fluff]

+Fluff New.xlsm
	A	B	C
1	Location	In Use?	Latitude
2	Wiltshire	Yes	51.11
3	Warwick	Yes	53.24
4	Teignbridge	Yes	54.29
5	St Albans	Yes	50.74
6	South Derbyshire	Yes	53.75
7	Sedgemoor	X	52.30
8	Richmondshire	Yes	53.83
9	North Devon	X	53.87
10	Liverpool	Yes	X
11	Kirklees	Yes	51.26
12	Kingston upon Hull	Yes	X
13	Hyndburn	Yes	51.13
14	Erewash	Yes	53.79
15	Dover	Yes	53.07
16	Chester	Yes	52.97
17	Cheshire East	Yes	53.59
18	Calderdale	Yes	X
19	Bury	X	51.80
20	Bradford	X	51.32
sheet2

+Fluff New.xlsm
	A	B
1	Location
2	Bradford	Text
3	Bury	Text
4	Calderdale	Different text
5	Cheshire East
6	Chester
7	Dover
8	Erewash
9	Hyndburn
10	Kingston upon Hull	Different text
11	Kirklees
12	Liverpool	Different text
13	North Devon	Text
14	Richmondshire
15	Sedgemoor	Text
16	South Derbyshire
17	St Albans
18	Teignbridge
19	Warwick
20	Wiltshire
Sheet1

Cell Formulas
Range	Formula
B2:B20	B2	=IF(COUNTIFS(sheet2!A:A,A2,sheet2!B:B,"X")>0,"Text",IF(COUNTIFS(sheet2!A:A,A2,sheet2!C:C,"X")>0,"Different text",""))

 

[Fluff]

OP didn't mention column A in sheet2, just a check of columns 2 and 3.

Beg to differ.

I need to lookup cell A1 in sheet 1 in sheet 2 Range A1:F100.