VBA formula

Budge

Board Regular
Joined
Aug 24, 2002
Messages
75
I am trying to use a lookup in VBA where I need to search 2 columns.If one column has data,take that value or else look in the next column and take value.

My formula is saying ISNA Range1,8,0 then look in Range1,9,0 else Range1,8,0.
I am using the wrong formula because the lookup value exists in Range1 on both occasions.The formula works fine if the data was always in Col 8 but Col 9 will never be
read.

To get around this I have used a plain vanilla lookup then used If Then arguments to achieve the desired result.But very inefficient.

Could you guys please tell me how to get a lookup happening that has double scoops of chocolate chips with an assortment of nuts and caramel topping.

Much appreciated.
Budge
 

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

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi Budge:

How about posting some data that you are LOOKING up, the code you are using, the result you are getting, and the result you want to get. This will help in clearly understand your sitation, and getting meaningful responses.

Regards!

Yogi
 

Budge

Board Regular
Joined
Aug 24, 2002
Messages
75
Yogi,
My apologies.Thought conceptually would be enough.
My formula is
"=if(IsNa(Vlookup(" & Snum & ",Clean,8,0)),vlookup(" & Snum & ",Clean,9,0),vlookup(" & Snum & ",Clean,8,0))"

where Snum is my value to look up and I have created the range name CleanTerms

I need a value from either col8 or col9.Every time I getting whatever is in Col8.So if Col8 is blank take value in Col9.
But formula takes the blank as the value from Col8 when I need Col9.

Thanks
Budge



If col8 is 0 this is fine.Take 0 as value
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi Budge:

If I read it right from your description -- your formula should be:

'=IF(VLOOKUP(SNUM,Clean,8,0)="",VLOOKUP(SNUM,Clean,9,0),VLOOKUP(SNUM,Clean,8,0))

see the following worksheet simulation where I have used this formula for verification
Book1
ABCDEFGHI
1SNumlabel
2b9.6lookup_range
3lookup_value
4result
5
6SNumColcol2col3col4col5col6col7col8col9
7g2.43.44.45.46.47.48.49.4
8a2.53.54.55.56.57.58.59.5
9b2.63.64.65.66.67.69.6
10v2.73.74.75.76.77.78.79.7
11k2.83.84.85.86.87.89.8
12p2.93.94.95.96.97.98.99.9
Sheet1
</SPAN>

Please post back if it works for you ... otherwise explain a little further and let us take it from there.

Regards!

Yogi
 

Forum statistics

Threads
1,144,220
Messages
5,723,091
Members
422,477
Latest member
pete101

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
Top