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

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.

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

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?

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.