# Search & match two lists & return corresponding value

#### tkroper

##### Active Member
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>

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

#### AliGW

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

Excel 2016 (Windows) 32 bit
AB
1Example:
2Sheet1
38601(4)6.08
48813(1)10.26
59015(5)8.22
6
7Sheet2
886016.08
9881310.26
1090158.22
Sheet4
Cell Formulas
RangeFormula
B3=VLOOKUP(--LEFT(A3,4),\$A\$8:\$B\$10,2,0)

#### tkroper

##### Active Member
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

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

Last edited:

#### tkroper

##### Active Member

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

#### AliGW

##### Banned
Try it without the coercion:

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

#### tkroper

##### Active Member
Now it returns "#REF!"

#### tkroper

##### Active Member
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

No problem!

Replies
1
Views
474
Replies
10
Views
736
Replies
0
Views
1K
Replies
2
Views
637
Replies
3
Views
484

1,191,707
Messages
5,988,230
Members
440,139
Latest member
ngaicuong2017

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