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

ghrek

Board Regular
Joined
Jul 29, 2005
Messages
124
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,
 
Last edited:

ghrek

Board Regular
Joined
Jul 29, 2005
Messages
124
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,950
Office Version
365
Platform
Windows
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

Board Regular
Joined
Jul 29, 2005
Messages
124
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,950
Office Version
365
Platform
Windows
How many rows of data would you expect to get at a maximum?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,950
Office Version
365
Platform
Windows
In that case you can limit the formula to row 9000, although it probably won't make difference to speed.
 

Forum statistics

Threads
1,086,065
Messages
5,387,585
Members
402,070
Latest member
hyperf0

Some videos you may like

This Week's Hot Topics

Top