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

ghrek

Active Member
Joined
Jul 29, 2005
Messages
301
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:

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

ghrek

Active Member
Joined
Jul 29, 2005
Messages
301
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
62,465
Office Version
  1. 365
Platform
  1. 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

Active Member
Joined
Jul 29, 2005
Messages
301

ADVERTISEMENT

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
62,465
Office Version
  1. 365
Platform
  1. Windows
How many rows of data would you expect to get at a maximum?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,465
Office Version
  1. 365
Platform
  1. 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,141,474
Messages
5,706,597
Members
421,459
Latest member
drewber

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top