VLOOKUP

[nnadimi19]

Hi Every one, it has been a long time since I have had any question, but now I have a doozy one.

I have a sheet that has the following three columns:
Sheet#1
Class Code TDI Code Exposure type
11111 1 32
11111 1 46
22222 2 45
33333 3 47

I have another sheet has the following two Columns:
Sheet#2
Class Code Number of Exposures
11111 250
22222 300
11111 10

Is there a way I can lookup class code 11111 from sheet #2 in sheet #1 and if the number of exposures is >= 250 then exposure type will be 32 if number of exposures is <= 10 then the exposure type will be 46. the class codes and exposure types are not changeable.

 

[IML]

On 2002-10-25 13:50, nnadimi19 wrote:
Hi Every one, it has been a long time since I have had any question, but now I have a doozy one.

I have a sheet that has the following three columns:
Sheet#1
Class Code TDI Code Exposure type
11111 1 32
11111 1 46
22222 2 45
33333 3 47

I have another sheet has the following two Columns:
Sheet#2
Class Code Number of Exposures
11111 250
22222 300
11111 10

Is there a way I can lookup class code 11111 from sheet #2 in sheet #1 and if the number of exposures is >= 250 then exposure type will be 32 if number of exposures is <= 10 then the exposure type will be 46. the class codes and exposure types are not changeable.

So in this example the number your after on sheet 2 for 11111 is 260? Also what if exposures are between 10 and 250?

 

[Aladin Akyurek]

On 2002-10-25 13:50, nnadimi19 wrote:
Hi Every one, it has been a long time since I have had any question, but now I have a doozy one.

I have a sheet that has the following three columns:
Sheet#1
Class Code TDI Code Exposure type
11111 1 32
11111 1 46
22222 2 45
33333 3 47

I have another sheet has the following two Columns:
Sheet#2
Class Code Number of Exposures
11111 250
22222 300
11111 10

Is there a way I can lookup class code 11111 from sheet #2 in sheet #1 and if the number of exposures is >= 250 then exposure type will be 32 if number of exposures is<= 10 then the exposure type will be 46. the class codes and exposure types are not changeable.

It appears, upon a literal reading of your question, that you ask for...

In C2 in Sheet2 enter:

=IF(ISNUMBER(MATCH(A2,INDEX(Table,0,1))),((B2>=250)*32)+((B2<=10)*46),"?")

where Table refers to the data area in Sheet1, excluding the labels.
This message was edited by Aladin Akyurek on 2002-10-25 14:11

 

[nnadimi19]

I must have not written the question properly. I do not want to multiply any number all I want is that if the class code in sheet # 2 is 11111 and the number of exposures is 250 than I will have the third column in sheet #2 equal to the third column in sheet #1. The final result will as follows:

Sheet#2
Class Code Number of Exposures exposure Type
11111 250 32
22222 300 45
11111 10 46

 

[Aladin Akyurek]

On 2002-10-25 14:23, nnadimi19 wrote:
I must have not written the question properly. I do not want to multiply any number all I want is that if the class code in sheet # 2 is 11111 and the number of exposures is 250 than I will have the third column in sheet #2 equal to the third column in sheet #1. The final result will as follows:

Sheet#2
Class Code Number of Exposures exposure Type
11111 250 32
22222 300 45
11111 10 46

Try the formula though... It might inform you whether your question is worded as you intended.

 

[nnadimi19]

On 2002-10-25 14:57, Aladin Akyurek wrote:

On 2002-10-25 14:23, nnadimi19 wrote:
I must have not written the question properly. I do not want to multiply any number all I want is that if the class code in sheet # 2 is 11111 and the number of exposures is 250 than I will have the third column in sheet #2 equal to the third column in sheet #1. The final result will as follows:

Sheet#2
Class Code Number of Exposures exposure Type
11111 250 32
22222 300 45
11111 10 46

Try the formula though... It might inform you whether your question is worded as you intended.

Aladin; I tried the formula what it is doing it is entering the values 32 or 46 but the problem is that there might be other values in that column such as A or B or 6 etc. I think I must be doing something wrong. I do not understand this part of the formula ((B3>=250)*32)+((B3<=10)*46),"?")

 

[Aladin Akyurek]

On 2002-10-25 15:12, nnadimi19 wrote:

On 2002-10-25 14:57, Aladin Akyurek wrote:

On 2002-10-25 14:23, nnadimi19 wrote:
I must have not written the question properly. I do not want to multiply any number all I want is that if the class code in sheet # 2 is 11111 and the number of exposures is 250 than I will have the third column in sheet #2 equal to the third column in sheet #1. The final result will as follows:

Sheet#2
Class Code Number of Exposures exposure Type
11111 250 32
22222 300 45
11111 10 46

Try the formula though... It might inform you whether your question is worded as you intended.

Aladin; I tried the formula what it is doing it is entering the values 32 or 46 but the problem is that there might be other values in that column such as A or B or 6 etc. I think I must be doing something wrong. I do not understand this part of the formula ((B3>=250)*32)+((B3<=10)*46),"?")

Your question is underspecified. The formula is intended to expose that.

Try to word how you think the exposure type can/must be determined for a class code in Sheet2 by looking up in Sheet1.