×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

help with lookup features

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

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)

RE: help with lookup features

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.

RE: help with lookup features

(OP)
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.
 

RE: help with lookup features

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)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources