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

#### ghrek

##### Active Member
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.

Last edited:

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### ghrek

##### Active Member
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
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.

#### ghrek

##### Active Member

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
How many rows of data would you expect to get at a maximum?

#### ghrek

##### Active Member
I would say at very max 8000

#### Fluff

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

Replies
8
Views
139
Replies
2
Views
92
Replies
17
Views
290
Replies
7
Views
94
Replies
6
Views
119

1,141,476
Messages
5,706,604
Members
421,460
Latest member
Taamrak

### 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.

### Which adblocker are you using?

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

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