VBA code to lookup value in column

Zimmerman

Well-known Member
Joined
Jan 22, 2009
Messages
663
I have a userform that populates a value in column D. What I'm trying to get it to do when the OK button is clicked is to have a vlookup the value in that column. But it needs to perform a different lookup based on the value.
So if Cell D8 value = 1 then cell F8 lookup formula would be =vlookup(D2,'Pallet and Truss Data'!A9:Z250,5,False)
So if Cell D8 value = 2 then cell F8 lookup formula would be =vlookup(D2,'Pallet and Truss Data'!A9:Z250,9,False)
So if Cell D8 value = 3 then cell F8 lookup formula would be =vlookup(D2,'Pallet and Truss Data'!A9:Z250,13,False)
So if Cell D8 value = 4 then cell F8 lookup formula would be =vlookup(D2,'Pallet and Truss Data'!A9:Z250,17,False)
So if Cell D8 value = 5 then cell F8 lookup formula would be =vlookup(D2,'Pallet and Truss Data'!A9:Z250,21,False)
So if Cell D8 value = 6 then cell F8 lookup formula would be =vlookup(D2,'Pallet and Truss Data'!A9:Z250,25,False)

The Value it needs to lookup is in range D8:D15, D22:D29, D36:D43, D50:D57, D64:D71, D78:D85 and D92:D99

Thanks in advance
Excel 2016
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
The lookup formula can be just this:

=VLOOKUP(D2,'Pallet and Truss Data'!A9:Z250,$D$8*4+1,FALSE)

Im not sure what the line that begins with 'The Value' means.
 
Upvote 0
Yeah I apologize for speaking incorrect on 2 things. The D2 I have in the lookup should have been D8 like below.
So if Cell D8 value = 1 then cell F8 lookup formula would be =vlookup(D8,'Pallet and Truss Data'!A9:Z250,5,False)
So if Cell D8 value = 2 then cell F8 lookup formula would be =vlookup(D8,'Pallet and Truss Data'!A9:Z250,9,False)
So if Cell D8 value = 3 then cell F8 lookup formula would be =vlookup(D8,'Pallet and Truss Data'!A9:Z250,13,False)
So if Cell D8 value = 4 then cell F8 lookup formula would be =vlookup(D8,'Pallet and Truss Data'!A9:Z250,17,False)
So if Cell D8 value = 5 then cell F8 lookup formula would be =vlookup(D8,'Pallet and Truss Data'!A9:Z250,21,False)
So if Cell D8 value = 6 then cell F8 lookup formula would be =vlookup(D8,'Pallet and Truss Data'!A9:Z250,25,False)
And for the line that is "The Value" means there could be a value in cell D12 and then cell 12 would need to perform this lookup.
Or there could be a value in cell D93 and cell F93 would do this lookup. That's why I have them in ranges.
 
Upvote 0
Ok so check this formula works as intended.

=VLOOKUP(D8,'Pallet and Truss Data'!$A$9:$Z$250,D8*4+1,FALSE)
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

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