VBA formula

[Budge]

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

 

[Yogi Anand]

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

 

[Juan Pablo González]

Are you trying to do a double lookup ? I'm really not sure what you're talking about...

 

[Budge]

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]

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
	A	B	C	D	E	F	G	H	I
1		SNum							label
2		b	9.6						lookup_range
3									lookup_value
4									result
5
6	SNumCol	col2	col3	col4	col5	col6	col7	col8	col9
7	g	2.4	3.4	4.4	5.4	6.4	7.4	8.4	9.4
8	a	2.5	3.5	4.5	5.5	6.5	7.5	8.5	9.5
9	b	2.6	3.6	4.6	5.6	6.6	7.6		9.6
10	v	2.7	3.7	4.7	5.7	6.7	7.7	8.7	9.7
11	k	2.8	3.8	4.8	5.8	6.8	7.8		9.8
12	p	2.9	3.9	4.9	5.9	6.9	7.9	8.9	9.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