Trouble with Vlookup. Simple formula returning value error.

excuisite1

New Member
Joined
Oct 19, 2016
Messages
16
=iferror(VLOOKUP(N71:Q79,Monday!N59:Q67,21,FALSE)

Above is the formula I tried to use. It keeps returning #value!

So basically table 2 stays static. But table 1 will have different diameters and different # of welds every day.

All I want is for table 2 to look at table 1, see if any of the diameters match that day. If the diameters match then I want table 2 to update it's # of welds cell with the total welds for that diameter found in Table 1.

Table 1: Diameter (N59:Q67)
Table 1 & 2: # of Welds (column 21)
Table 2: Diameter (N71:Q67)

Table 1
Diameter# of Welds
2"15
4"2
8"8

<tbody>
</tbody>

Table 2
Diameter# of Welds
2"
3"
4"
5"

<tbody>
</tbody>
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
One problem is that you're looking for column 21, in a range that is only 4 columns wide (N:Q).

You need to fix that one way or another.
 
Upvote 0
Another problem is this

=iferror(VLOOKUP(N71:Q79,Monday!N59:Q67,21,FALSE)

This normally needs to be a single cell reference, or a single value.
 
Upvote 0
Hi. It can't be a single cell reference. The company I work for has merged cells and I can't make alterations to that extreme. But I've used merged cells in the past for this type of formula and they've worked perfectly fine.

Also, their are 30 columns (A to AD). I'm pretty sure you have to use the column number for the page, not for the range.

This is a formula I'm currently using for another purpose and it works perfectly
=IFERROR(VLOOKUP($A$17:$A$48,Friday!$D$14:$J$64,7,FALSE),"")
 
Upvote 0
In your second formula, the column reference of 7, will look up column J, the 7th column in the range D:J, am I right ?

With only 4 columns from N:Q, looking for column 21 is impossible.
 
Upvote 0
Image - TinyPic - Free Image Hosting, Photo Sharing & Video Hosting

This is what I'm trying to work with. There is more rows above the picture. But for the purposes of this formulas its the ones in the areas I have previously mentioned.

If someone could suggest a change to my formula (like actually writing out an alternative) it would be appreciated. I've been working with it all day now and I'm out of ideas.
 
Last edited:
Upvote 0
would you be apposed to a UDF as a replacement to VLookup? I use this less temperamental might help

Code:
Public Function NotBadVLookup(MatchValue As Variant, InputRng As Range, RowOffset As Long, ColOffset As Long) As Variant
Dim LoopCell As Range
Dim NewRow As Long
Dim NewCol As Long
 If InputRng.Count < 10000 Then 'Prevents entire sheet frome being sent in as a range
  For Each LoopCell In InputRng
   If MatchValue = LoopCell.Value Then
    NewRow = LoopCell.Cells(1, 1).Row + RowOffset
    NewCol = LoopCell.Cells(1, 1).Column + ColOffset
    If InputRng.Parent.Cells.Rows.Count > NewRow And NewRow > 0 And InputRng.Parent.Cells.Columns.Count > NewCol And NewCol > 0 Then
     NotBadVLookup = LoopCell.Cells(RowOffset + 1, ColOffset + 1)
     GoTo 1
    Else
     'Continue loop in hopes next match will be valid
    End If
   End If
  Next LoopCell
 End If
 NotBadVLookup = ""
1
End Function
 
Last edited:
Upvote 0
Thank you so much :)

I'm not opposed to using anything that will return the information I want. I really appreciate you helping out.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

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
Back
Top