Help with excel formulas

Heedtheadvice

There you have it!

My preference is always whenever possible to use named ranges.

Firstly you see something in the formula that is meaningful such as "my_lookup_list" (in your case the lookup list range A1:B6 on a sheet would be named  "my_lookup_list")

Secondly if you expand the list by adding another row, for example by inserting a row with data in the cells, the range is automatically expanded

Plus you do not need to use $ to fix the cells when you copy.

It is also worthwhile doing two other things to your database/list.

First insert anothe column thus moving your data each one column higher (A1 to C6 with your data now in cols B and C) and add an numeric ident to col A. (in this case 1 to 6, would be 1 to 77 for your full list). This means that your numeric column need not be in numerical order, you can sort the whole list alphabetically by the text column, or sort by the ident column to get back to entered list sort. Col C can hold any number you like, even duplicates if so wished.

Adding a row at the top allows you to put headings above each column - again makes it readable and more meaningful. When sorting you can set option to ignore the title row.

All that is not always showing the benefit but can be very useful if you find you have errors such as in data entry, duplicates, empty cells etc during troubleshooting.

Were_Doomed

Sandtree said:

Were_Doomed said:

If there is no corresponding value returned by the VLOOKUP you'll get a #N/A result. You can handle this by preceding the VLOOKUP formula by using:

=IF(ISNA(VLOOKUP(etc.)),"The value you want to show for a #N/A result",VLOOKUP(etc.))

This is how most people write the above but I wonder why the below formula isnt more commonly used? In principle it feels as if it should be more efficient....

=IFERROR(VLOOKUP(etc),"The value you want to show for a #N/A result")

That should also work, but is actually a few more characters to type/copy.  

Heedtheadvice

The only problem with such iferror tests is in this circumstance the error comes from an unexpected reason!!

It is wise to use best practice and ensure you do not get unexpected errors (explicit like #na, or where just the wrong data is returned .-it can happen!) Lots of advice on the web if you just search "excel vlookup formula" even from memory such as https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1

WaywardDriver

If I'm the only user, I wouldn't bother with error trapping as any incorrect text would be obvious with a N/A vlookup result.
However VLOOKUP is more usually used when a short numerical value or code is entered, and a longer text value is returned, which is the opposite of what the OP is wanting to do. If this is the case I'd suggest using Data Validation with Allow=List (of valid values i.e. ONE1 etc) to enter the text values with VLOOKUP to return the numerical values.

FinallyStoppedLurking

Tanks everyone. Will revist this when I come to make some changes. I've got it working in a basic format for now.

Sandtree

Were_Doomed said:

Sandtree said:

Were_Doomed said:

If there is no corresponding value returned by the VLOOKUP you'll get a #N/A result. You can handle this by preceding the VLOOKUP formula by using:

=IF(ISNA(VLOOKUP(etc.)),"The value you want to show for a #N/A result",VLOOKUP(etc.))

This is how most people write the above but I wonder why the below formula isnt more commonly used? In principle it feels as if it should be more efficient....

=IFERROR(VLOOKUP(etc),"The value you want to show for a #N/A result")

That should also work, but is actually a few more characters to type/copy.  

Its less
if(isna() -v- iferror( = 1 character save
,vlookup(etc) -v- '' = 13 or more characters saved

Though less of keystrokes I dont know how Excel works in the background such that if you do =IF(ISNA(VLOOKUP(etc.)),"The value you want to show for a #N/A result",VLOOKUP(etc.)) and the ISNA is false does Excel repeat the vlookup for the false response or does it recall the value from the ISNA function. Obviously in tiny speadsheets it wont make a difference but have seen some massive "applications" created in excel with 0.5m plus rows where vlookups can take time.

Heedtheadvice

I appreciate what you write wayward so answer me this.

If one is looking for best match rather than an equality in the lookup list and the list is not sorted alphabetically,...do you get the correct answer or might there be an error?

and what happens if there are duplicate text entries in the list with different numeric values, is that picked up or could you get returned one of (and possibly the unwanted) value returned?

If I remember correctly one will return an untrappped error and the other a potentially unseen error that could look correct! I appreciate the Op has asked about a lookup that return for an identical match but for completeness and in case of a change then it should be covered.

Sandtree

Vlookup returns the first match so if there are multiple matches it returns the first in the list from top to bottom.

Vlookup can also do a "best match" but my experience it gives some odd results sometimes.  Depending on what exactly you are wanting to achieve and how you want to define "best match" you may be better off writing your own custom function

click86

If I understand this question correctly, you have a list of manually typed values in Column A.

In column B, you want to assign a sequential count, only applicable to non-blank values.

So, if your data starts in cell A1, you can add this formula to cell B1.

=IF(A1<>"",COUNTA($A$1:A1),'''')

Replicate the formula down to suit and this should do what you need.

WaywardDriver

The last parameter in VLOOKUP is TRUE for an approximate match or FALSE for an exact match. If omitted TRUE is assumed.
An invalid value e.g. VLOOKUP("ONE23",$A$1:$B$77,2,TRUE) returns 2 while VLOOKUP("ONE23",$A$1:$B$77,2,FALSE) returns N/A. Data Validation using a List gets round this by preventing an invalid value being entered.