Search (formula,vba) to compare 7cells

  • Thread starter Thread starter The Wraith
  • Start date Start date
T

The Wraith

Hello,

--1-- I have 6-cells a row (A till F), filled with numbers
--2-- A1 small, till G1 great (1 to42 value)
--3-- Rows goes till 3000 ( full filled with numbers)

--4-- Other 6 cells give me 6-random numbers
in allso 6 separeted cells.. (from small till great)


What i look = to check if a complete random of 6-cells
allready exist im my list till 3000 ,a kind of comparing formula..

If 6 numbers in a row allready exist to give row number out of th
3000..

Greeting
 
Hi
try the following array entered formula (entered with CTRL+SHIFT+ENTER)
if your lookup cells are A1:F1 on a separate sheet:
=IF(ISNA(MATCH(A1&B1&C1&D1&E1&F1,'sheet1'!A1:A3000&'sheet1'!B1:B3000'sh
eet1'!C1:C3000&'sheet1'!D1:D3000'sheet1'!E1:E3000&'sheet1'!F1:F3000,0))
,"no match","Row number: " &
MATCH(A1&B1&C1&D1&E1&F1,'sheet1'!A1:A3000&'sheet1'!B1:B3000'sheet1'!C1:
C3000&'sheet1'!D1:D3000'sheet1'!E1:E3000&'sheet1'!F1:F3000,0))
 
Here is a non VBA way

=MATCH(J1&K1&L1&M1&N1&O1,A1:A3000&B1:B3000&C1:C3000&D1:D3000&E1:E3000&F1:F30
00,0)

I am confused by your 6 cells, a1 - g1 are 7 cells?

If they are in A1:F1 see the above, adapt to fit if they are in a1 - g1

the above formula assumes the numbers you want to test are in J1:O1
and the formula has to be entered with ctrl + shift & enter
if nothing is found it will return #N/A if found it will return the row
number
 
Ok i gona examine the formula ,
and put it on my worksheet..

Thx very much ...

Till Later.
 
Back
Top