Excel Magic Trick # 158: VLOOKUP w 2 Variables As Criteria

VLOOKUP & OFFSET function Amazing Trick! See what to do if we have VLOOKUP with Duplicates in first column of lookup table AND we have two variables as criteria for VLOOKUP! We can use the VLLOKUP function to retrieve the data and the OFFSET function to look at just part of the desired table.
See the OFFSET function used as a Dynamic Range in VLOOKUP Function.

Category:

Science & Technology

Tags:

• Excel
• Two
• Variables
• As
• Criteria
• For
• VLOOKUP
• OFFSET
• function
• Mike
• Gel
• Girvin
• Highline
• Community
• College

[snowpilot795]

If you can please :)

1) Excel WB with database of information. A number value is assigned to each row of information.

2) Excel WB that calls on #1 Workbook, using VLOOKUP, for values in that row, and then I add values manually. Several worksheets all doing this in this #2 WB

3) Excel WB with a sheet that seeks to determine when, in #2, a sheet has a percentage value related to an ID number in #1 Workbook. I want to aggregate those %s from #2 WB into #3 WB for a total % related to an ID. Tnx!

[ExcelIsFun]

@snowpilot795 , I am not sure how to do that. Sorry.

[caraes81]

Hi Fun Excel man.

How would you go about summing the return value of a criteria. The criteria repeats it self multiple times with different values. I would like to sum all this values.

Thanks

Great stuff

[ExcelIsFun]

Try this video title:

Excel Magic Trick 320: Lookup Adding: SUMPRODUCT & SUMIF

[SupremeParis]

How would you go about adding in a 3rd variable? This worked perfectly for a while but now a 3rd variable needs to be added.

Help pls :)

[ExcelIsFun]

Dear SupremeParis,

I would switch to a different tactic (not VLOOKUP). See this Playlist for various methods:

Various Excel Extract Records From Database Table / List

If you do not know how to find the Playlists, search for and watch this video title:

Search For Excel Videos Download Excel Files excelisfun

--excelisfun

[ExcelIsFun]

You are welcome!

[skiNchocolate]

Hey can you help me with EXCEL...I.T

[ExcelIsFun]

Dear ssalazarjunior,

Or maybe you can just have a one record (one line) for each employee for each day and have the Time In Before Lunch, Out Before Lunch, In After Lunch, Out After Lunch, then a formula at the end to get the time worked?

--excelisfun

--excelisfun

[ExcelIsFun]

Dear ssalazarjunior,

VLOOKUP looking for the day +1 is very clever to get the second day, but cant you just use the VLOOKUP without the +1 for the first date?

--excelisfun