# VLOOKUP

##### Board Regular
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

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### IML

##### MrExcel MVP
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?

##### MrExcel MVP
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.

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

##### Board Regular
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

##### MrExcel MVP
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.

##### Board Regular
On 2002-10-25 14:57, Aladin Akyurek 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),"?")

##### MrExcel MVP
On 2002-10-25 14:57, Aladin Akyurek 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.

Replies
7
Views
477
Replies
7
Views
226
Replies
2
Views
257
Replies
3
Views
257
Replies
0
Views
295

1,181,096
Messages
5,928,048
Members
436,586
Latest member
latintxn

### 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.

### Which adblocker are you using?

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

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