help with lookup features
help with lookup features
(OP)
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?





RE: help with lookup features
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:
ht
Please ask if you need more help.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: help with lookup features
=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)
RE: help with lookup features
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.
RE: help with lookup features
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.
RE: help with lookup features
=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)