VLOOKUP Issues

• I need help with making vlookup go all the way across my columns and down. Is it possible to have lookup_value and column_index both be variable values? If it isn't possible, is there another way this can be done?

As of right now, I got the vlook up to go across, but the the cell value stays the same all the way across, I would need it to change going from one column to another, if this makes sense.

• Re: VLOOKUP Issues

The problem is that in each cell it is 'finding the same values, C7 and N5. You could do this 2ways I think...

1). Copy this formula into cell N7, and then use Fill to drag across the sheet. Excel will then update the necessary references as normal.

2). Alter your code to make it so that C7 and N5 are dependant on which cell is being filled. Potentially much harder but probably prettier.

Alastair

Einstein:
Things should be made as simple as possible, but not any simpler

Be sure to check out TemplateZone for all your Microsoft Office Needs.

• Re: VLOOKUP Issues

Example of FIll:

.

HTH

Alastair

Einstein:
Things should be made as simple as possible, but not any simpler

Be sure to check out TemplateZone for all your Microsoft Office Needs.

• Re: VLOOKUP Issues

Hello zuko,

Quote

I need help with making vlookup go all the way across my columns and down. Is it possible to have lookup_value and column_index both be variable values? If it isn't possible, is there another way this can be done?

Possibly, like this...

Code
``````For Each ws In Worksheets
If Not ws.Name = "Sheet1" Then
ws.Select
Range(Cells(7, 14), Cells([B]nRow, nCol[/B])) = _
"=VLOOKUP(C7,Sheet1!\$A\$2:\$BB\$3000,Match(N5,Sheet1!\$D\$1:\$BB\$1,FALSE)+3,False)"
Range("A1").Select
End If
Next ws
Sheets("Sheet1").Select``````

If I've been helpful, let me know. If I haven't, let me know that too.

• Macro to run a macro in another workbook

Hi i want to rit a macro which in the background opens an existing excel file runs a macro in the that file copies the generated values from the file back to the first excel file.

TIA

talshe

• Macro to run macro in another workbook

Hi i want to rit a macro which in the background opens an existing excel file runs a macro in the that file copies the generated values from the file back to the first excel file.

TIA

talshe

• Re: VLOOKUP Issues

Einstein:
Things should be made as simple as possible, but not any simpler

Be sure to check out TemplateZone for all your Microsoft Office Needs.

• Re: VLOOKUP Issues

Ahh! I think I get it.
In which case the best way and easiest way to do this is to set a row of numbers in the row above your data, (row 4). Starting with 4 in G4 and then 5 in H4...all the way along. You can then hide this column or colour the cells white so they are hidden. Then link the column number to those hidden values.

EG: =VLOOKUP(\$C7,DBSS!\$A\$2::\$X\$396,G\$4,FALSE) then you can copy this down and the column reference will update according to the row number. Can also be done in VBA if you really want, but this is so much simpler!

Alastair

EDIT: Once this is doen for the first cell you can again use AutoFill in the same way as my original suggestion or you can use:

Sub test()

As an extension to your original code.

HTH

Einstein:
Things should be made as simple as possible, but not any simpler

Be sure to check out TemplateZone for all your Microsoft Office Needs.

• Re: VLOOKUP Issues

You could also reference the column and adjust as desired within your code. if your lookups increment by one as they go across.

For example, Column G is the 7th column and you wish to lookup the 4th column in your lookup table. Thus subtracting three from your column will give you the right number. Of course, this won't work well if you want the 6th column of data in your lookup table when you go to column H.

Just a thought, providing your data "cooperates".

HTH,

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!