• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Lookup

M

Mohandas

Member
I have 2 Sheets

1 Sheet is named RDBN
2 Sheet is is named Brand

I Need to get the details from RDBN Sheet to Brand Sheet

Example

For LS in brand sheet it should get the details from AJ Columns, Brand Names are there in b Coulmn in RDBN Sheets

As of now i am giving vlookup by specifying the column number.

I need more easier way

Regards

Mohandas
 

Attachments

  • Book1.xlsx
    425 KB · Views: 7
Hi Mohandas,

As the sheet is, it won't quite work at the moment. You need to make sure that the values in Brand!B5:H5 match exactly the values you want to lookup against.

This is a formula you can use to do what you want, providing those details are matched (also, un-merge the cells in Brand!B5:H5 if you can, because formulas don't like merged cells very much).

You would put this in Brand!B8: =IFERROR(INDEX(RDBN!$A:$BE,MATCH(Brand!$A8,RDBN!B:B,0),MATCH(Brand!B$5,RDBN!$10:$10,0)),"")

And copy formula to the other cells on the Brand sheet.

For more information on how to do 2-way lookups, check out the actual chandoo site, here: http://chandoo.org/wp/2010/11/09/2way-lookup-formulas/ - a lot of this sort of thing is available without having to start a topic ;)

File uploaded to show you the formula - I have changed B5 to refer to "Sandeep" which picks up his values, but apart from that, none of the other names match, so excel can't recognise them.

Cheers,
cb
 

Attachments

  • Book1.xlsx
    429.4 KB · Views: 4
Hi Mohandas,

The formula that caladanbrood provided picks up the information in column G because the name sandeep appears several times in your columns and it picks up the first one. The easiest way to solve this would be to make the names unique entries, for example by adding a number: sandeep 1, sandeep 2, etc.
Depending on the name written in the Brand Sheet (including number), it would pick the correct value.

Would this work for you?

Cheers,
 
Hi Mohandas,

Please see attached file for a quick-fix solution. Simply copy the formula where you want/need it.

But I do agree with the others that you need to make changes in your file for reliable solutions. First step would be tables (ctrl + T). This will force you towards a better design of your raw data. Next step would be (in my opinion) removing every (sub-)totals in your raw-data. After that I think setting up a simple PivotTable will be as easy as pie.
 

Attachments

  • Book1 (1).xlsx
    434.7 KB · Views: 3
Hi Mohandas,

I was talking about the raw data. Making your raw-data easy to process should help with every other report/analyze in the future because you can automate it.

That been sad, I highly doubt that your company is so suborn to not being able to adapt.
 
Hi Mohandas,

You can explain your required with reference to cell numbers, can't find Sandeep in column B!!
 
IN RDBN SHEET Column C to AB are Actuals Cases and From AF to BE are in ton .

in Brand Sheet i need to get the tons for the respective sales officer.

AJ ,AT,AP are sales officers totals

LS= Sandeep in RDBN Sheet
 
If you have a list of what letters refer to what name, you could add an additional vlookup clause to the formula, to look at the data in Brand!B5:H5 and pick what name is is referring to.

e.g =IFERROR(INDEX(RDBN!$A:$BE,MATCH(Brand!$A8,RDBN!B:B,0),MATCH(vlookup(Brand!B$5,namereftable,2,FALSE),RDBN!$10:$10,0)),"")

However, changing the names to be consistent throughout the document would be good practice for future reference and also easier, so it's up to you really :)
 
Also, just change $A:$BE to $AF:$BE if that is what you want to refer to, I was just guessing as unfortunately there was not too much detail in your post about what you wanted!
 
Hi Mohandas,

it is even more confusing with your new sheet, plz pick up an example from this sheet and explain the process from A to Z. :)
 
You must log in or register to reply here.
Back
Top