VLOOKUP

nnadimi19

Board Regular
Joined
Jul 14, 2002
Messages
240
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.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

IML

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,743
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

Board Regular
Joined
Jul 14, 2002
Messages
240
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

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

Board Regular
Joined
Jul 14, 2002
Messages
240
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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.
 

Forum statistics

Threads
1,143,640
Messages
5,719,987
Members
422,257
Latest member
Calion

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top