Monthly Archives: March 2012

Tom Urtis: Lookup with 2 or more criteria

Today’s post from Tom Urtis involves finding the record that matches two criteria, such as Chevrolet in column A and 4-Door in column B. The solution involves INDEX, MATCH entered as an array formula, so this is ranked pretty high on the Excel spicy scale. Read the complete article here.


Rahim Zulfiqar Ali: “Getting Started with New Ideas on LOOKUP”

From Rahim’s Post:
 
The Excel lookup function (LOOKUP, VLOOKUP and HLOOKUP) are not case sensitive. For example, if you write a lookup function to look up the text rahim, the function considers any of the following a match: RAHIM, Rahim or RaHiM
Example:
1. Create the sheet given below:
 
The figure below shows a simple example. Range D2:D7 is named Range1, and range E2:E7 is named Range2. The word to be looked up appears in cell B1 (named Value).  … To Read More Click Here to go to Rahim Zulfiqar Ali’s Lesson.

 

 

 

 

You may find Rahim Zulfiqar Ali Google Sites

“VLOOKUP, OFFSET and MATCH in PowerPivot” from Javier Guillén

In this example, Javier is simulating (in PowerPivot) the output achieved through double lookups using VLOOKUP, OFFSET and MATCH.

One of those really great functions in the native Excel environment is OFFSET.  In combination with other functions like MATCH, it can generate a type of ‘double lookup’ that can be really helpful when creating dynamic reports.  For example, the table below describes a matrix defining a coordinate of units for each combination of ‘Band’ and ‘Group’. …To Read More Click Here to go to Javier Guillén’s Lesson

You may find Javier on Twitter @javiguillen or at his blog Javier Guillén on WordPress


Que Publishing: “Video Podcasts on VLOOKUP Formulas “

The QUE blog checks in for some mid-week Excel-lence:

“For day three of VLOOKUP Week, this two-part video podcast featuring Bill Jelen demonstrates the VLOOKUP Formulas in Excel. Bill Jelen, author of Excel 2010 In Depth offers a beginning and advanced look at VLOOKUP in these two videos. In video 1, matching data from two lists:! …Click here to continue on to the Videos presented at  Que Publishing Blog.

 


From The MS Office Blog: “VLOOKUP Tutorial: Updating prices in a master product list”

From Emily Warn at Microsoft Excel Blog:

“In honor of VLOOKUP Week, MVP Bill Jelen has created a VLOOKUP tutorial for those of you who have a basic working knowledge of Excel but want to improve your skills. You most likely know the core set of formulas that come with Excel and have heard that VLOOKUP is an easy way to look up a value in an Excel workbook, speeding up getting work done. But that’s about it.”… Click here to continue at Blogs.Office.Com

 


Colin Legg and “Binary Searches With VLOOKUP”

Check out this entry from Colin Legg: “Binary Searches With VLOOKUP”

“I went for a job interview about a year ago and the interviewer made a passing comment that he didn’t understand why anyone would ever pass TRUE into VLOOKUP’s  range_lookup parameter. There are lots of cases when you might want to do this so I thought a few, nice examples would be a great way to get VLOOKUP week underway.” To Read More Click Here to go to Colin Legg’s Lesson.

PLEASE NOTE that Colin will continue to contribute Excel-lent articles this week for VLOOKUP WEEK 2012! Be sure to check them out at Colin Legg on WordPress

To find more from Colin, visit his blog Colin Legg on WordPress


Neil Malek “Fun with VLOOKUP”

Neil has prepared a video for VLOOKUP WEEK 2012 entitled “Fun With VLOOKUP”!

“In this episode, Neil shows how the MATCH function and a little Data Validation can make the VLOOKUP function more user-friendly.”

 

You may find Neil Malek at TV.KnackTraining.Com


Colin Legg looks at “Why Does VLOOKUP Return An #N/A Error?”

Colin writes:

VLOOKUP week enters its second day, meaning that it’s time to have a look at how to problem-shoot #N/A errors. On the online forums I frequently see frustrated users asking why their VLOOKUP formula returns an #N/A error, so I hope that some of the reasons I’ve listed below will be helpful to many of you.” …To Read More Click Here to go to Colin Legg’s Lesson

To find more from Colin, visit his blog Colin Legg on WordPress


From Somkiat Foongkiat: “สูตร VLookup” [VLookup Formula]

VLookup in Plain Thai! Yes, Thai! MS Excel MVP Somkiat Foongkiat has sent in a contribution for our Thai Excel-lers!

“แม้การใช้คำสั่ง Data > Sort และ Data > Filter สามารถช่วยค้นหาข้อมูลที่ต้องการมาแสดงได้ง่าย ถ้าใช้คำสั่ง Sort ย่อมส่งผลทำให้ลำดับรายการของข้อมูลเดิมเปลี่ยนไป ต่างจากการสั่ง Filter ซึ่ง” คลิกที่นี่เพื่ออ่าน

Somkiat Foongkiat may be found at ExcelExpertTraining.com


Javier Guillén “Simulates an “approximate match” VLOOKUP in PowerPivot”

Javier’s first entry begins:

“It is often said that relationships in PowerPivot work in a similar way to VLOOKUPs.  In reality, this is only partially true. If we examine the claim a bit closer we realize that Excel’s VLOOKUP function has a parameter in which it is possible to use an approximation when matching values against the lookup table…”  To Read More Click Here to go to Javier Guillén’s Lesson

 

You may find Javier on Twitter @javiguillen or at his blog Javier Guillén on WordPress


Follow

Get every new post delivered to your Inbox.

Join 53 other followers