Vlookup Macro

[bhieatza]

I have a Vlookup function that works great but I can not figure out how to get it working in a macro. The function is
=IF(ISNA(VLOOKUP(AU2,Sheet1!F:F,1,0)),"",VLOOKUP(AU2,Sheet1!F:G,2,FALSE))
an it is posted in column AP and copied all the way down. Any suggestions?

Thanks for your help.

 

[bhieatza]

[TABLE="width: 1138"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]D.Services[/TD]
[TD]D30.HVAC[/TD]
[TD]D3020.Heat.Generating.Systems[/TD]
[TD]D3021.Boilers[/TD]
[TD]Boiler, Electric, 101 to 144 kW, Replace[/TD]
[/TR]
[TR]
[TD]20836[/TD]
[TD]D.Services[/TD]
[TD]D30.HVAC[/TD]
[TD]D3020.Heat.Generating.Systems[/TD]
[TD]D3021.Boilers[/TD]
[TD]Boiler, Electric, 101 to 144 kW, Replace[/TD]
[/TR]
</tbody>[/TABLE]

I changed yours above to reflect AP2 and nothing comes up in the column(first row first column above). The second row is with the vlookup function. I don't know what i am doing wrong.

 

[L. Howard]

=IF(ISNA(VLOOKUP(AU2,Sheet1!F:F,1,0)),"",VLOOKUP(AU2,Sheet1!F:G,2,FALSE))

So am I reading your formula correct...

If AU2 value is in not column F, then return "" (nothing) otherwise look in column F for AU2 value and return the value in G.
of the same row.

SO if AU2 value is not in column F there is nothing for value_if_true to return and nothing for value_ if_false to return.

Going further if vlookup cannot match the lookup value it will return #N/A and your formula with the ISNA is on the lookout for #N/A and if found then returns "".

Then the value_if_false portion can't find the same value in the same column either.

Is that correct or am I screwed up...

Howard

 

[bhieatza]

So am I reading your formula correct...

If AU2 value is in not column F, then return "" (nothing) otherwise look in column F for AU2 value and return the value in G.
of the same row.

SO if AU2 value is not in column F there is nothing for value_if_true to return and nothing for value_ if_false to return.

Going further if vlookup cannot match the lookup value it will return #N/A and your formula with the ISNA is on the lookout for #N/A and if found then returns "".

Then the value_if_false portion can't find the same value in the same column either.

Is that correct or am I screwed up...

Howard

if there is no value in AU2 then return nothing. if there is a value in AU2 then look for that value in column f of sheet 1 and return the corresponding value found in column g of sheet 1 in cell AP2

 

[L. Howard]

Okay, try like this:

=IF(AU2="","",VLOOKUP(AU2,Sheet1!F:G,2,FALSE))

Howard

 

[bhieatza]

Yes that works the same way mine did. Now i am trying to make that into a macro....any suggestions?

 

[L. Howard]

How about posting a link to an example workbook along with a re statement of what you want to happen and where.

Refer to specific cells, rows, columns in you instructions.

You cannot attach a workbook, but a link is okay. I use drop box to do that but there are other link utilities. No photo or shared workbook please.

I will take a look and see if we can get you going.

Howard