Exact match look up formula.

JmyCrikitt

New Member
Joined
May 14, 2012
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Thanks in advance for your assistance.
I have a column of item numbers in Sheet A col. C that I would like to look up the exact item number in sheet B Col b and return freight value associated in sheet b Col D back to Sheet A Col F.

if the item number doesn't exist in Sheet B would like to return 0.00

Appreciate any help - thanks
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Try something like this in column F of Sheet A (check the sheet name and relevant range)

Excel Formula:
=IFNA(VLOOKUP(C2,'Sheet B'!B$2:D$100,3,0),0)
 
Upvote 0
I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Try something like this in column F of Sheet A (check the sheet name and relevant range)

Excel Formula:
=IFNA(VLOOKUP(C2,'Sheet B'!B$2:D$100,3,0),0)
Thank you for quick response - I must be doing something wrong as it's returning #REF!

I've always just used lookup (I'm old school) though that won't return 0.00 or NA if the item code isn't listed on the look up tab. Since I'm on my work system unable to upload a mini-sheet. Suggestions on how to edit my current lookup for exact match? =LOOKUP(C4,'Freight Look up'!$A$2:$A$526,'Freight Look up'!$C$2:$C$526)

Thanks
 
Upvote 0
Thank you for quick response - I must be doing something wrong as it's returning #REF!

I've always just used lookup (I'm old school) though that won't return 0.00 or NA if the item code isn't listed on the look up tab. Since I'm on my work system unable to upload a mini-sheet. Suggestions on how to edit my current lookup for exact match? =LOOKUP(C4,'Freight Look up'!$A$2:$A$526,'Freight Look up'!$C$2:$C$526)

Thanks
Also thanks for the tip I did update my account details :)
 
Upvote 0
=VLOOKUP(C4,'Freight Look up'!$A$2:$C$526,3,FALSE)
I've gotten this far & it works - returns #N/A when no item code match - any suggestion to edit for return value of 0 when no match?

Appreciate it. thanks
 
Upvote 0
=VLOOKUP(C4,'Freight Look up'!$A$2:$C$526,3,FALSE)
I've gotten this far & it works - returns #N/A when no item code match - any suggestion to edit for return value of 0 when no match?

Appreciate it. thanks
That looks exactly like the middle part of the formula I posted except that the sheet name and columns have changed since post #1.
All you have to do is what I did with my VLOOKUP formula - wrap it in a IFNA() function. ;)

=IFNA(VLOOKUP(C4,'Freight Look up'!$A$2:$C$526,3,FALSE),0)
 
Upvote 0
Solution
You're welcome. Thanks for the follow-up. :)
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top