Search & match two lists & return corresponding value

[tkroper]

Help!

I have two sheets, each with 4-digit codes in column A (except some codes in sheet1 have 5 digits). Sheet2 also contains dollar amounts in column B.


I need a formula for sheet1 that will return the dollar amount from sheet2 for the matching code.

Example:
Sheet1
8601(4)
8813(1)
9015(5)

Sheet2

8601	6.08
8813	10.26
9015	8.22

<tbody>
</tbody><colgroup><col><col></colgroup>

 

[AliGW]

This should do it (you will need to adjust cell and range references accordingly):

Excel 2016 (Windows) 32 bit
	A	B
1	Example:
2	Sheet1
3	8601(4)	6.08
4	8813(1)	10.26
5	9015(5)	8.22
6
7	Sheet2
8	8601	6.08
9	8813	10.26
10	9015	8.22
Sheet4

Cell Formulas
Range	Formula
B3	=VLOOKUP(--LEFT(A3,4),$A$8:$B$10,2,0)

 

[tkroper]

Thank you, Ali! All I get is "#N/A" :-{

Could it be because the list on sheet1 is not in numerical order but sheet2 is?

 

[AliGW]

It works fine here. More likely you've broken it in adapting it. Can you please post the formula as you have adapted it?

This is an exact match, so the order doesn't matter.

 

[tkroper]

Here's how I adapted it:

=VLOOKUP(--LEFT(A4,4),'PP Rates'!$A$5:$A$496,2,0)

 

[AliGW]

Try it without the coercion:

=VLOOKUP(LEFT(A4,4),'PP Rates'!$A$5:$A$496,2,0)

 

[tkroper]

Now it returns "#REF!"

 

[tkroper]

I found my error - my range should read 'PP Rates'!$A$5:$B$496.

Thank you so much, Ali!! I appreciate you for your help on this

 

[AliGW]

No problem!