Matching data between 2 sheets and returning a value to sheet 1

[techiemom60]

Good Morning,

On sheet 1, I want to enter an amount (this amount will be entered)
Let's say for this, I will enter 2.500

I want to use the entered amount and the vendor from sheet 1, to look up the % of cost, where the vendor matches the vendor and pull the % of cost to sheet 1, if the amount entered exists for that vendor between the min and max value for that vendor.

I have two worksheets, set up as follows:

Sheet 1:

Value to determine percent (this amount will be entered)

Vendor % of Cost
ABCD .35
BCDE .50
CDEF
DEFG

Sheet 2:

Vendor Min Value Max Value % of Cost
BCAD 2.501 2.555 .35
CDAB 1.222 2.500 .25
ABCD 2.000 2.450 .10
BCDE 2.350 2.555 .50
CDEF 1.755 2.451 .65

Thanks in advance for any assistance.

[sweep]

Hi,

Index and match is the best way to go about this.

=INDEX(Sheet2!$A$1:$D$6,MATCH(Sheet1!A2,Sheet2!$A$1:$A$6,0),4) to return % of cost into column B

HTH,

Dave

[starguy]

Good Morning,

On sheet 1, I want to enter an amount (this amount will be entered)
Let's say for this, I will enter 2.500

I want to use the entered amount and the vendor from sheet 1, to look up the % of cost, where the vendor matches the vendor and pull the % of cost to sheet 1, if the amount entered exists for that vendor between the min and max value for that vendor.

I have two worksheets, set up as follows:

Sheet 1:

Value to determine percent (this amount will be entered)

Vendor % of Cost
ABCD .35
BCDE .50
CDEF
DEFG

Sheet 2:

Vendor Min Value Max Value % of Cost
BCAD 2.501 2.555 .35
CDAB 1.222 2.500 .25
ABCD 2.000 2.450 .10
BCDE 2.350 2.555 .50
CDEF 1.755 2.451 .65

Thanks in advance for any assistance.

if you enter Vendor name in col A of sheet1 try this in col B to return % from sheet2.

=vlookup(A1,sheet2!$A$1:$D$10,4,false)

[techiemom60]

Thank you. Will this index and match formula, match to the entered number at the top of the worksheet -- the value entered to determine the %'s to be returned?

[starguy]

Thank you. Will this index and match formula, match to the entered number at the top of the worksheet -- the value entered to determine the %'s to be returned?

please put some example of your requirement.

[techiemom60]

Originally Posted by techiemom60
Good Morning,

On sheet 1, I want to enter an amount (this amount will be entered)
Let's say for this, I will enter 2.500

I have two worksheets, set up as follows:

Using Cell B2, the entered amount and the vendor from sheet 1, look up the % of cost, where the vendor on sheet2, matches the vendor on sheet1, the min and max amount contains the value entered in cell B2, and return the % of cost from sheet2 to sheet 1, if the amount entered exists for that vendor.

Sheet 1:

Cell B2: (this amount will be entered and will always be different)

Vendor % of Cost
ABCD .35
BCDE .50
CDEF
DEFG

Sheet 2:

Vendor Min Value Max Value % of Cost
BCAD 2.501 2.555 .35
CDAB 1.222 2.500 .25
ABCD 2.000 2.450 .10
BCDE 2.350 2.555 .50
CDEF 1.755 2.451 .65

Thanks in advance for any assistance.