How to use Lookup Array vs V or H Lookup

[jae113]

Hi All,

This relates to an earlier post of mine: http://www.mrexcel.com/forum/showthread.php?t=557976

I've never really used Lookup before, so I have a few questions:
Does the array have to be in seperate cells, how does it match up the values? If for example I put Americas in A1 and the matching value, 90, in B2, how does the array match them? If I do have to put the values in a cell together, is there a .... I guess qualifier is the word? Would it be Americas=90?

When I looked in Excel help, it suggested V or H lookup. I'm not clear of the benifits of either for what I'm doing.

Thanks!

 

[jae113]

Ok! I got the lookup table to work! Awesome! Is there a way to refer to a lookup table in a different work sheet. It's important that it not print.

 

[Aladin Akyurek]

Ok! I got the lookup table to work! Awesome! Is there a way to refer to a lookup table in a different work sheet. It's important that it not print.

A2, Sheet1

B2, Sheet1

In B2 enter:

=VLOOKUP(A2,Sheet2!$C$2:$D$100,2,0)

This formula looks up A2 on Sheet2, by matching A2 against Sheet2!$C$2:$C$100. If the match succeeds, the formula returns an associated value from $D$2:$D$100, the second column.

 

[Derek Brown]

If you go to www.youtube.com and search for "mrexcel lookup" you will find several podcasts on this subject.
"excelisfun" also provides great podcasts - look for the "duelling" podcasts created by Bill and Mike.

 

[jae113]

Ok, but I think what I want it to do is different. I want it to look up a value array on a different sheet, Sheet2, perform a calculation on sheet1 and then show the answer on sheet1.

In plain-er English: I want it to see if B2 on Sheet1 is equal to Basell. If it is, I want it to see that Basell has a policy of 90 days (that would be the value array on Sheet2), and then look at column I on Sheet1 to see what the current day is (probably something like 78). Then what I want to show in the "answer" of final column is "12 days before demurrage on TODAY". With 12 being the answer generated when it calculates I at 78, goes to the lookup table to see Basell is equal to 90, and subtracts 90 - 78.

Now that I'm looking at it, I don't know that what I said is clearer English! Does all that make sense?

 

[jae113]

I got it!!!!!!!! Now all I have to work out is the TODAY function! Thank you so so so so much for all your help!

 

[jae113]

If you go to www.youtube.com and search for "mrexcel lookup" you will find several podcasts on this subject.
"excelisfun" also provides great podcasts - look for the "duelling" podcasts created by Bill and Mike.

I will def do that - thanks!