LinkBack URL
About LinkBacks
I am trying to have a Vlookup that is able to pull the second and third values though from the second sheet.
Could someone lend a hand please as i have tried several different methods on my main sheet but getting nowhere.
Thanks in advance.
I am trying to have a Vlookup that is able to pull the second and third values though from the second sheet.
Could someone lend a hand please as i have tried several different methods on my main sheet but getting nowhere.
Thanks in advance.
Try...
In D3:
Formula:
Please Login or Register to view this content.
Copy across.
Edit: Added 2nd condition for C3.
?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
― Robert A. Heinlein
Try this in D3:
=INDEX(Sheet2!$D:$D,SMALL(IF(Sheet2!$C$2:$C$4=$C3,ROW(Sheet2!$B$2:$B$4)),COLUMNS($A:A))) Ctrl Shift Enter
Drag the formula to the right.
Edit: CK beat me to it! Win some lose some...
Would you be able to break down how this formula is created so that I would be able to integrate it into the main workbook?Originally Posted by 63falcondude
Regards.
Sure.
Sheet2!$D:$D is the column where the values are that you want to return.
Sheet2!$C$2:$C$4 is where you are looking for "1 Test Lane".
ROW(Sheet2!$B$2:$B$4) just returns the row number of the match. I meant to change that from B to C but it doesn't make a difference.
COLUMNS($A:A) returns 1, then when it is dragged one cell to the right, it turns into COLUMNS($A:B) which returns 2, then 3, etc.
Last edited by 63falcondude; 01-08-2019 at 01:00 PM.
I have managed to update the example sheet to better depict the sheet I'm using but without any confidential information.
I'm trying to get the formula to work in the red area but it just errors out. any help welcome.
Regards.
I'd recommend restructuring your "Manifest Data" sheet. To flattened table structure. Or at least have same category column in one location. It will make subsequent lookup/analysis so much easier.
Ex:
0.JPG
Unfortunately i am unable to change the Data source as this is how it comes though. so need to work with the data source i have already.
The 'Manifest Data' worksheet is set up poorly so the formulas used to pull from there will be more complex than should be necessary.
You can try this in Shops!N6:
Ctrl Shift Enter
Please Login or Register to view this content.
Drag the formula to the right and down.
Thats working, however if the Address shows in both the West and East it only pulls the Plot from West and wont show the East side plot number.
Sorry, but this isn't the case at all. You can easily create another worksheet with a copy of the data in a layout suitable to this task. There's nothing stopping you from making it easier for yourself to crunch the data.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
I'm not sure what you mean. The formula from post #9 produces the exact results that you had in the workbook from post #6.
The COUNTIF function tells the formula which column to look at.
@63falcondude
I think OP means that when another record for "1 Test Lane" is added in East-DED1 section (F column).
The formula does not look for match in F column. Since match is already found in C column.
Personally can't think of smart way of dealing with this. It's probably far easier to create intermediate flat table to base lookup on.
Oh... The sample did not indicate this possibility.
I 100% agree with this.@63falcondude
It's probably far easier to create intermediate flat table to base lookup on.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks