Lookup cell value and return matching all data from another workbook.

**larzep** · 05-20-2013, 12:53 AM

Hi ladies & gents,

I've been scratching my head vigorously for the past few days now trying to come up with a formula to do the following.

In workbook 1 (WB1), look up cell value in G1 & G2 (cheque date & State) and return all results from table array in workbook 2 (WB2), which contains that date.

Pretty much all i want the spreadsheet to do is that when ever i punch in a date in cell G1 & the state in G2, it would look up in the table array in another workbook and pull through all transactoins that happend on that date.

I have tried the simple formula of vlookup but it only returns the first lookup result.

I am really new to this so not familiar with other formula strings.

Your help would be greatly appreciated.

Thank you.

Best Regards,

John

**benishiryo** · 05-20-2013, 12:59 AM

hi John, try this array formula in A4:

Formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL-SHIFT-ENTER to activate the array. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL-SHIFT-ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

**larzep** · 05-20-2013, 01:14 AM

Hi Benishiryo,

That is simply amazing! its working like a charm. Thank you soo much for your prompt reply.

Just a quick question, is there any chance you can briefly explain what each part of the formula mean or do? This way i can have an understanding so that when i need to make any modifications to it, i can some what make sense of where to edit or what to manipulate?

If its too much trouble then thats ok.

Just would like to really undertand this so i might be able to apply it to other scenarios.

:D

Cheers mate

**FDibbins** · 05-20-2013, 01:16 AM

If by workbook 1 and workbook 2, you actually mean worksheet 1 and worksheet 2 - within the same workbook - , then try this approach (you cant use exactly the same method if you did mean 2 different workbooks/files, just add in the file name)...

On sheet2 add 2 helpers - I used H and I
In I, copied down...
=A2&B2
in I, copied down...
=H2&COUNTIF($H$2:H2,H2)

next, make sure the headings on each sheet are identical - change where needed (Amt to Amount, etc)
then A4, copied down and across use this...
=IFERROR(INDEX(Data!$A:$I,MATCH('Lookup Result'!$G$1&'Lookup Result'!$G$2&ROW(A1),Data!$I:$I,0),MATCH('Lookup Result'!A$3,Data!$A$1:$I$1,0)),"")

(we could probably put an array formula together, but if you have a ton of data, that may slow things down)

**larzep** · 05-20-2013, 01:26 AM

Hi FD,

Thanks also for your response. I did mean two seperate workbooks/files. I only consolidated it into one with the example1.xls because i thought it would make it easier to see. i have incorporated the formula from Benishiryo and have changed the file name in the formula and it works fine. I just want to get some what of an explanation to his formula so fully get my head around it.

Also why is it not a normal formula with the = sign but instead you have to use the ctrl+Shift+enter

**benishiryo** · 05-20-2013, 03:35 AM

the INDEX formula typically has 3 parts people normally use; array, row_num & column_num. i'm just going to use 2 here

array
this will be the range of data my desired results are. i used Data!D$2:D$178 without fixing the column, so that i can copy over the formula & it'll return me Data!E$2:E$178 later.

row_num
manually looking in the "Data" sheet, what i want to return are in row 174:178. but because my array is from Data!D$2:D$178, my 1st row is row 2. hence, what i want to return is the 173rd row to 177th row.

IF(Data!$C$2:$C$178=$G$1,IF(Data!$B$2:$B$178=$G$2,
so what i want to achieve is to create a 2 logical tests. if the columns in C is equals to G1 AND the columns in B is equals to G2, show me the row number based from D2.

ROW(Data!D$2:D$178)-ROW(Data!D$2)+1))
the row number can be obtained using ROW(Data!D$2:D$178). but that will give me the numbers 2:178, the actual row number of those cells. the row number i need is based from D2. so if i minus the 1st row i start from, it will return me 0:176. i can't possibly start from row 0, so i added "+1" in the end.

if you select this portion of formula in the formula bar & press F9 to calculate:
IF(Data!$C$2:$C$178=$G$1,IF(Data!$B$2:$B$178=$G$2,ROW(Data!D$2:D$178)-ROW(Data!D$2)+1))
you will get a series of FALSEs. and when you scroll all the way to the end, you should see those that passed the 2 logical tests will show the row numbers. not going to show all here, but something like:
{FALSE;FALSE;FALSE;FALSE;......173;174;175;176;177}

SMALL(IF(Data!$C$2:$C$178=$G$1,IF(Data!$B$2:$B$178=$G$2,ROW(Data!D$2:D$178)-ROW(Data!D$2)+1)),ROWS(A$4:A4))
for our first entry in A4, we need to return the first smallest number from that array of FALSEs & row numbers. so in A4, i want to obtain 173. in A5, i want 174, and so on. you can therefore use SMALL to help you. ROWS(A$4:A4) is different from using ROW. with an "S" behind, it counts the number of rows in the range given. i used A$4:A4, so that's 1 row in total, and not return 4 for row 4. since i fixed the row, copying down will increase to 2 rows in total; returning the 2nd smallest number in the array

and here's an explanation of array formulas.
http://www.cpearson.com/excel/ArrayFormulas.aspx

hope that helps

**larzep** · 05-22-2013, 06:46 PM

Hi Ben,

OMG you are simply fantastic. Truely amazing with all your help. Apologies for taking up so much of your time in helping me with this but I really appreciate your time and help.