Can anyone help on how to extend and amend a V LOOK UP

[ghrek]

Hi

Im very new on formulas / V LOOK UP'S and worked out whats below so far and it compares data in column J against data in column B and then put a "0" in to identify missing.

=IF(IFERROR(VLOOKUP(J1,$B$1:$B$9999,1,FALSE),"missing")="missing", 1, 0)

What im trying to do is do the same but also include the following

Column K data to look up against column C of which I believe is =IF(IFERROR(VLOOKUP(K1,$C$1:$C$9999,1,FALSE),"missing")="missing", 1, 0)
Column L data to look up against column D =IF(IFERROR(VLOOKUP(L1,$D$1:$D$9999,1,FALSE),"missing")="missing", 1, 0)
Column M data to look up against column E =IF(IFERROR(VLOOKUP(M1,$E$1:$C$9999,1,FALSE),"missing")="missing", 1, 0)

and if all the data not an exact match across all 4 columns I need the work MISSING inserted and not the number "0"

Also at the moment it looks in rows 1-9999 but is there anyway I can set it up so it looks to last entry will be any numbers before 9999. If you can that dont matter but the matching up of data is important.

Any help greatly appriciated.

Many thanks in advance,

 

[Fluff]

Surely that's what the formula posted here does. https://www.mrexcel.com/forum/excel...-more-efficently-post5351317.html#post5351317

 

[ghrek]

Sorry I weren’t clear. That link looks for duplcates of which is what I part need but what I need for this is data in rows. B/C/D/E to be an exact match with data in columns J /K/L/M. When I mean an exact match I mean across the row in all 4 columns. So let’s say data in row 99 columns B-E is an exact match with data in row 2000 in columns J-M that’s good and nothing to be done but if data in columns B-E but not in J-M then I need column F on row concerned marked missing.

Thing is also data in columns J-M will be spread all over different rows to what’s in columns B-E so need it to look all the way down J-M for a match with B-E. Hope that cleared misunderstandings up.

 

[Fluff]

This formula from the other post
=IF([@Source]="","",IF(ISNA(MATCH([@ID]&"|"&[@Field2]&"|"&[@Date]&"|"&[@Value],INDEX($J$2:$J$9999&"|"&$K$2:$K$9999&"|"&$L$2:$L$9999&"|"&$M$2:$M$9999,0),0)),"Missing",""))
Adds the word "Missing" if there is no exact match.
In what way is that different to what your asking here?

 

[ghrek]

Donno if its me or not but that formula seems extremely slow and it to me seems to not always pick out missing items.

Is it because I have it set looking at 9999 lines? Is there anyway I can amend so it only looks at rows with data in as the will be different every time or do I have to specify a specific number?

 

[Fluff]

How many rows of data would you expect to get at a maximum?

 

[ghrek]

I would say at very max 8000

 

[Fluff]

In that case you can limit the formula to row 9000, although it probably won't make difference to speed.