help with lookup features 1

[eit09]

I think I need to use hlookup and vlookup in the same formula but not sure how to do this. I have attached my file and there are two workbooks. One labeled output (raw data) and the other labeled Loads (data organized from output). If you take a look at the Loads worksheet B2 is the value I would like to obtain from a formula (versus typing each value individually). This value is found if you look under M406 and LC15 in the output worksheet. Could anyone explain or show how to write a formula that will take B1 & A2 from the load worksheet and find the corresponding value in the output worksheet?

 

[IDS]

You can use the DGET() or DMAX() functions together with a data table.

Because one of your criteria looks like a cell address there is a catch with the criteria you need to look out for. Check out the Excel help and/or look here:

http://newtonexcelbach.wordpress.com/2009/09/09/using-the-dmax-function/

Please ask if you need more help.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[iken]

Assuming you want to fill out data on Loads worksheet, you can also use the following:

=VLOOKUP(A2&C1,Output!C2:E91,3,FALSE)

Note, A2&C1 combines these two cells into one. You will also need to add a helper column on Output sheet (i have this in Column C.

If you are wanting to fill out data on Output sheet, then use:

=HLOOKUP(B3,Loads!$C$1:$E$15,A3-13,FALSE)

The A3-13 gets row ref (2 in this case)

 

[kris44]

First let me say the best method is using "Helper" or "Calculation" columns as iken suggests, but if for some reason you want to use just one formula without adding any columns here goes

If you paste the following formula into cell B2 of Loads & then copy across & down you will match what you have done.

=INDEX(INDIRECT(ADDRESS(MATCH(B$1,Output!$B:$B,0),1,,,"Output")):INDIRECT(ADDRESS(MATCH(B$1,Output!$B:$B,0)+100,4,,,"Output")),MATCH($A2,INDIRECT(ADDRESS(MATCH(B$1,Output!$B:$B,0),1,,,"Output")):INDIRECT(ADDRESS(MATCH(B$1,Output!$B:$B,0)+100,1,,,"Output")),0),4)

This relies on Member (Column B of Output sheet) column being grouped as you currently have it.

 

[eit09]

kriss44,
I was able to paste your equation in the attached spreadsheet and it worked great. Unfortunately I am unable to get it to work in my new spread sheet. I have attached my new spread sheet to this reply. I was wondering if you could do a similar equation as before in this new spread sheet? I want to do the same thing as the previous spreadsheet (fill in the table in the loads workbook from the values in the output workbook). I changed the output columns a bit and I think that is why I cannot get your previous formula to work. I'm hoping that if you can give me another formula I can compare the two and be able to understand these complicated functions.

 

[kris44]

Paste this formula into Cell B3

=INDEX(INDIRECT(ADDRESS(MATCH($A3,Output!$A:$A,0),1,,,"Output")):INDIRECT(ADDRESS(MATCH($A3,Output!$A:$A,0)+100,4,,,"Output")),MATCH(B$2,INDIRECT(ADDRESS(MATCH($A3,Output!$A:$A,0),2,,,"Output")):INDIRECT(ADDRESS(MATCH($A3,Output!$A:$A,0)+100,2,,,"Output")),0),4)