How to create a lookup function or VBA code which can return value against only first found result?

user3200076 Published at Dev

user3200076

Suppose, in Sheet1, I have an Order table

OrderNo.  ShipmentNo.  Rate
54321          AP2312
54321          AP2312
29781          AP2312
32181          AP2312
54321          AP2312

In sheet2, I have rate table

OrderNo.     Rate
S4321          65

the below table is the end result which I am trying to get by applying some kind of lookup based on sheet2 rate table

OrderNo.  ShipmentNo.  Rate
54321     AP2312       65
54321     AP2312       0
29781     AP2312       NA
32181     AP2312       NA
54321     AP2312       0

which means, the formula should return the value against only first found result viz. 54321 in this case.

Subodh Tiwari sktneer

You may try this

On Sheet1 In C2

=IFERROR(IF(COUNTIF(A$2:A2,A2)=1,VLOOKUP(A2,Sheet2!A:B,2,0),0),"NA")

Edit: Or more correctly this

=IF(ISNUMBER(MATCH(A2,Sheet2!A:A,0)),IF(COUNTIF(A$2:A2,A2)=1,VLOOKUP(A2,Sheet2!A:B,2,0),0),"NA")

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-04-17

Comments

0 comments

How to return first non-blank value with LOOKUP function

Single test recursion - how to create the "no result found" return value?

How to create function which return only even numbers in array?

How to create a lookup function or VBA code which can return value against only first found result?

How to create a lookup function or VBA code which can return value against only first found result?

Failed to listen on localhost:8000 (reason: Cannot assign requested address)

Loopback Error: connect ECONNREFUSED 127.0.0.1:3306 (MAMP)

How to import an asset in swift using Bundle.main.path() in a react-native native module

pump.io port in URL

Compiler error CS0246 (type or namespace not found) on using Ninject in ASP.NET vNext

BigQuery - concatenate ignoring NULL

ngClass error (Can't bind ngClass since it isn't a known property of div) in Angular 11.0.3

ggplotly no applicable method for 'plotly_build' applied to an object of class "NULL" if statements

Spring Boot JPA PostgreSQL Web App - Internal Authentication Error

How to remove the extra space from right in a webview?

java.lang.NullPointerException: Cannot read the array length because "<local3>" is null

Jquery different data trapped from direct mousedown event and simulation via $(this).trigger('mousedown');

flutter: dropdown item programmatically unselect problem

How to use merge windows unallocated space into Ubuntu using GParted?

Change dd-mm-yyyy date format of dataframe date column to yyyy-mm-dd

Nuget add packages gives access denied errors

Svchost high CPU from Microsoft.BingWeather app errors

Can't pre-populate phone number and message body in SMS link on iPhones when SMS app is not running in the background

12.04.3--- Dconf Editor won't show com>canonical>unity option

Any way to remove trailing whitespace *FOR EDITED* lines in Eclipse [for Java]?

maven-jaxb2-plugin cannot generate classes due to two declarations cause a collision in ObjectFactory class

Any way to remove trailing whitespace FOR EDITED lines in Eclipse [for Java]?