• 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.

VLOOKUP, MATCH or other options

Y

Yulechka

New Member
Hello!
I am looking for the help with IF, VLOOKUP and MATCH formulas. Maybe there are other options here, but it is beyond of my knowledge. I browsed thru the similar topics, but my solution based on the explanation there does not work. Unfortunately, I can’t post the file, will post the screenshot of similar file and will try my best to explain.
The file has a few tabs where some tabs are the data tabs updated with copying and pasting from external source and some tabs with formula pulling certain info from the data tabs. I need to pull the total $$ from Tab 2(data) for the code on Tab 1. Sometimes, the col $$ on Tab 2 is moved to far right so the number of $$ col for VLOOKUP formula changes every month. And, is it possible to identify the codes that are listed in Tab 2 but not on Tab 1?
I have trouble most of the time to build the long formula sentences with multiple criteria. It is easier to build just a VLOOKUP of simple IF sentence, but when I need to combine them, I am stuck.
Thank you in advance for all your help.
clip_image001.png
 
Hi ,

The image has not loaded because the link is wrong ; please correct the link , and ensure the image is loaded.

Narayan
 
Sorry about that. It worked on my computer before.
I have copied into a simple version of it. The original file has a tons of data and the long list of codes and parameters.
Thank you again.
 

Attachments

  • VLOOKUP case.xlsx
    10.4 KB · Views: 7
Hi,

Since you are saying that the column with Total $ will vary, than in that case see the attached file. If you file data headers don't have a blank cells in between. Than a combination of VLOOKUP & MATCH can work. I had defined a named range Header which is dynamic. Go through it in Name manager. Just try this approach in your original file and see if you are getting the results or not?

Regards,
 

Attachments

  • VLOOKUP case.xlsx
    10.9 KB · Views: 7
Thank you so much, Somendra! This formula does not work yet as I think my problem now is with the naming range. When you said that the headers don't have a blank cells in between, does it apply to the range from the first named col and the last named col? If I put the range with extra spare col, does it still consider a blank?
 
@Yulechka

What I meant by not having blanks in between headers was on your Data Sheet headers are in the range B2:G2, it should be continuous every time you copy & paste data. It should bot be like B2:D2 there are headers and than E2 is blank and than again the headers continues. If that is the case than you may have to either clean up your data or you have to give a static named range such as
=DATA!$B$2:$Z$2 or what ever max you expect your data can be.

Regards,
 
You must log in or register to reply here.
Back
Top