CODEWITHSUNDEEP
excelexcel-formulaexcel-2010excel-2007
Robert Keegan
Robert Keegan

Reputation: 1

Formula to match data with multiple options and extract highest value each time

Having trouble with some data I am trying to organise for a personal project.

My reference master data is 10 digits long and my incoming data is only partially complete at 8 digits long, however the 8 digits are the same as the first 8 digits as the 10 digit data set.

A number of the 8 digit values could have more than one match in the 10 digit data set but my aim is to match it to the highest value and extract this next to my 8 digit values

Is there a formula possible for this or what would be the best way to go about this?

This is an example of how my data should look, is it possible ?

Master 10   8 digit incomplete  extracted max value

6403999810  64039998    Nil

6403999890  64039998    6403999890

6404110000  64041100    6404110000

6404191000  64041910    6404191000

6404199000  64041990    6404199000

Upvotes: 0

Views: 49

Answers (1)

OverflowStacker
OverflowStacker

Reputation: 1338

Are you looking for something like this Array Formula CTRL + SHIFT + ENTER

=MAX(IF(TEXT(B1,"@")=LEFT(IF($A$1:$A$9<>"",$A$1:$A$9),8),$A$1:$A$9))

Put into D1 and pull down.

You can insert other conditions in the first part fo the IF-function with AND.

enter image description here

Upvotes: 1

Related Questions