Thread: IF - ISTEXT - VLOOKUP STATEMENTs

1. IF - ISTEXT - VLOOKUP STATEMENTs

I'm trying to tie a "IF" Statement with a "ISTEXT" and a "VLOOKUP" and I'm not having much successs. I am attaching a sample of what I'm strying to do. Be appreciative for any help.

2. MNN,

It's hard to test this without the lookup file but here are a couple of things I noticed.
'=IF(ISTEXT(A7)="TRUE",VLOOKUP(A7,'FY 11 ACTUAL - FY 12 PROJ'!\$A\$10:\$AD\$472,20,FALSE),"",(VLOOKUP(A7,'FY 11 ACTUAL - FY 12 PROJ'!\$A\$10:\$AD\$472,20,FALSE)))

Changes,
=IF(ISTEXT(A7),VLOOKUP(A7,'FY 11 ACTUAL - FY 12 PROJ'!\$A\$10:\$AD\$47220,FALSE),VLOOKUP(A7,'FY 11 ACTUAL - FY 12 PROJ'!\$A\$10:\$AD\$472,20,FALSE))

You don't need the ="TRUE" since ISTEXT returns true or false.
IF only takes 3 arguments Test, True Action, False Action...thus I removed the "" which would be a 4th argument. Unless of course you wanted the False Action to return a blank then you would need a close paren after the "" and get rid of the following code.
I also don't understand why both VLOOKUPs are identical?

It would help if you could attach the other workbook or a like sample and explain exactly what the formula is supposed to return under what circumstances.

3. Hi

See attached file.
It seems you are trying to return the value from sheet [SOURCE] column [H] for the item matching the entry in column [A] on sheet [DB].

So you can use the MATCH function to look for an exact match.
Then you can use INDEX to return the value.
If it can't find the match, the IFERROR tells it to leave a "" instead.

zeddy

4. The Following User Says Thank You to zeddy For This Useful Post:

MNN (2012-05-28)

5. Thank you both for the alternatives. I am getting some #N/A returned as the result. Is there a way to add an "ISERR" and just get a blank space in the result field.

I have to look into the functionality of the "MATCH & INDEX" function. I really never used them and therefore I do not know much about them. Are they as credible and as easy as the "VLOOKUP" function. How does each one work individually and collectively?

Thank you both again.

6. Hi

If you =IFERROR(xxxxxxx,yyyy), this says do whatever the formula xxxxxxxx says, but if it returns an error, then just put what's in yyyy instead.

MATCH and INDEX are together more delightful to use than VLOOKUP.

When searching a whole column, MATCH can be used to find the row number for the matching item e.g. row 27.
(It can also return the column when searching a row).

INDEX(K:K, row) simply returns the entry from column [K] for the given row.
So, used together, it's just..
=INDEX(AW:AW,MATCH(what, where, FALSE)) to return an entry from column [AW]
(If it's on another sheet, point to the required column to have the sheet name included in the formula).
FALSE means look for an EXACT match.

If MATCH can't find what you are looking for, it will return #N/A
So, use =IFERROR( ..to deal with this.

Hope this helps

zeddy

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•