Vlookup and IF statements

[GrayMPA]

I use Vlookup a lot, and often inside IF statements. My formula runs something like this: =IF (Vlookup(a1, Othersheet!A1:f300,2,false)="Wave",1,(Vlookup(a1,othersheet!A1:f300,2,false)). Do I have to keep using the vlookup function at both sides of the IF statement? Is there a better way/idea to handle these kinds of problems?

Thanks in advance

Bob

 

[brucesw]

Hi Bob,

Why are you using an IF statement? Couldn't you replace "Wave" with 1 in your othersheet list?

I often use something like =IF(ISERROR(Vlookup(a1,othersheet!A1:f300,2,false),"Not found",Vlookup(a1,othersheet!A1:f300,2,false)) to replace #N\A with something ledgible.

Let me know,

Bruce

 

[Brian from Maui]

Hi Bob,

Why are you using an IF statement? Couldn't you replace "Wave" with 1 in your othersheet list?

I often use something like =IF(ISERROR(Vlookup(a1,othersheet!A1:f300,2,false),"Not found",Vlookup(a1,othersheet!A1:f300,2,false)) to replace #N\A with something ledgible.

Let me know,

Bruce

Bruce,

Here's a thread on ISERROR

http://216.92.17.166/board2/viewtopic.php?t=72183&postdays=0&postorder=asc&start=10

 

[brucesw]

Thaks Brian.

I actually got caught in a bad thread some time ago because of ISERROR versus ISNA etc on my lookups. I've learn't my lesson.

No reply from Bob yet.

PS What Hawaii like in March/April?

Cheers, Bruce

 

[Brian from Maui]

PS What Hawaii like in March/April?

Cheers, Bruce

Bruce,

Just like the rest of the months.....BEAUTIFUL!!!!

 

[GrayMPA]

Actually, what I get from the Vlookup function is a number. If it is a number, I need to get other information. If it is not, I can stop.

What I am trying to do is pull information from a production schedule. The schedule only tells me what I am to run; I have to look on other databases to find what additional supplies I need. These other databaases are not linked or easily accessible, so I cant pull directly from them. Besides, I am doing this on the sly - the computer folks don't know I am doing this. My aim is to give my people on the floor the ability to pull all the information they need at one time, and not waste time looking for it. If I can prove this saves time, I think I can get the computer folks to hard wire this process. Writing this macro isnt my full time job.

 

[brucesw]

Bob,

Welcome to the justification club! I seem to spent a lot of time building reports in Excel with vba Macros then passing details on to IT. Lucky for me our IT reporting team includes an ex-analyst from our team so we have an insider. :wink:

Did you know that VLookup alone gives a single dimensional result (ie lookup one value return another) but a combination with HLookup or the way I do it, using Offset and Match functions can give a result from 2 dimensions. For example; if you have a table with staff names down the left and product categories across the top (ie pivot table) then you can "lookup" the number of sales in a chosen category, by staff member.

Good luck,

Bruce