Finding exact number match in column. If no match, remove one number and check next column

[v0idern]

Hello fellas,

I have a problem that i haven't seen a solution for anywhere.

I have 6 columns with numbers: (A2:A400 ... G2:G400) that is a check list of numbers.

$H$1 contains a cell with a number value that should be checked against the check list columns, and always contains 8 digits, 65126548 for instance

However:
Column A contains 2-digit numbers
Column B contains 3-digit numbers
Column C contains 4-digit numbers
Column D contains 5-digit numbers
Column E contains 6-digit numbers
Column F contains 7-digit numbers
Column G contains 8-digit numbers

I'm trying to find a neat formula that first checks for a perfect matcht for H1 against check list column G2:G400, then removes one digit from the right and checks against column F (against 651265)... etc. and then stops after checking column A for 2-digit match (against 65).

Does anyone know a neat way to do this without using data bases?

Appreciate your help!

[martindwilson]

do you want all matches ?

[bebo021999]

For instance H1=12345678
Creating a list of 1-7 digits from the right: {12345678,1234567,123456,12345,1234,123,12}

INT($H$1*10^-{0,1,2,3,4,5,6})

Check with COUNTIF to figure out where in A2:G4 meets any of above list:

COUNTIF($A$2:$G$4,INT($H$1*10^-{0,1,2,3,4,5,6}))

and position:

MATCH(1,COUNTIF($A$2:$G$4,INT($H$1*10^-{0,1,2,3,4,5,6})),0)

Finally with INDEX to show the first match from the right columns:

=INDEX(INT($H$1*10^-{0,1,2,3,4,5,6}),MATCH(1,COUNTIF($A$2:$G$4,INT($H$1*10^-{0,1,2,3,4,5,6})),0))

File attached
Not sure it meets yours, but this may be a start.

[dredwolf]

Maybe Something like this :MulticolumnNumSearch.sol1.xlsx

[ChemistB]

I took a different approach (using Martin's sheet (thank you Martin)), in H2
=LEFT($H$1,9-ROW())+0 copied down

In I1 copied down
=MATCH(H1,INDEX($A$2:$G$7, ,8-ROW()),0)+1
gives row where match is found.
See attachment

[v0idern]

Thanks a lot for your kind replies, all!

I can see that i was a bit unclear in my questions, and bebo021999 you hit it almost spot on! Thanks!

Is it possible to alter the function easily so that it just produces a 1 value if a match, and 0 if not? (I actually needed both that and the returning value)