 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / April 2008VLOOKUP OR MATCH FORMULA
I have following data in my sheet. (please see below) Rows A B C F G---col 1 CODE AMT CODE_2 CODE K12----headings 2 SS 50 K12 SS 3 DD 20 S12 DD 4 FF 30 K12 FF 5 GG 10 E12 GG 6 HH 44 T12 HH 7 JJ 60 T12 JJ 8 KK 66 V12 KK 9 UU 70 S12 UU 10 PP 80 Y12 PP 11 ZZ 90 P12 ZZ I want formula in in cell "G2" which should match or lookup value of cell "G1" in range "C2:C11" and then match or lookup value of cell "F2" in range "A2:A11" and then bring up value from range "B2:B11" . Like in above table formula should bring value "50" in cell "G2" from column "B". I want formula something like this "lookup(G1 in C2:C11 then F2 in A2:A11 then bring value from column B). I hope I explained what I am trying to say. Please if anybody can help. Hi, I'm sure someone can help but i'm not clear what you are asking. Are you looking to lookup a value in a range and then return a value in another column on the same row where it was found? Eg lookup K12 in a2:f11. If the value of K12 is found in a7 return the value in b7 If not please try and explain again. > Hi, > [quoted text clipped - 7 lines] > > If not please try and explain again. look K12 in range(C2:C11) and SS which is in cell F2 in range(A2:A11) then bring value from range(B2:B11) the value should come 50 as if you see above table in row 2 we have SS in cell A2 and K12 in cell C2 so formula should pick cell B2 value which is 50. Basically with vlookup you lookup on value and then bring the answer but i want some formula which should lookup 2 values same time and then bring the result. i hope you understand what i am tring to say. OK got you. =IF(AND(VLOOKUP(G1,C2:D11,1,FALSE)=G1,VLOOKUP(F2,A2:B11,1,FALSE)=F2),VLOOKUP(F2,A2:B11,2,FALSE), 0) > OK got you. > > =IF(AND(VLOOKUP(G1,C2:D11,1,FALSE)=G1,VLOOKUP(F2,A2:B11,1,FALSE)=F2),VLOOKUP(F2,A2:B11,2,FALSE), > 0) Thanks for replying anon. i tried your formula and its work fine but when i drag this down it not working the way i want. the formula should only bring value from the row where both other value matches. it works fine in G2 cell but as i go down draging the formula its not working. any help you can give on this. thanks That's because the formula will be changing the ranges you're searching in, look carefully at what has happened when you drag it down, you will see G1 has changed to G2 or G3 etc, as will all of the cells you have dragged it to. You need to put a $ in front of the cells you don't want to change. =IF(AND(VLOOKUP($G$1,$C$2:$D$11,1,FALSE)=$G$1,VLOOKUP($F$2,$A$2:$B $11,1,FALSE)=$F$2),VLOOKUP($F$2,$A$2:$B$11,2,FALSE),0) Suggest getting a good excel book to learn the basics of this otherwise it'll be slow going for you to do your spreadsheet. > That's because the formula will be changing the ranges you're > searching in, look carefully at what has happened when you drag it [quoted text clipped - 7 lines] > Suggest getting a good excel book to learn the basics of this > otherwise it'll be slow going for you to do your spreadsheet. thanks for the advise about learning the basics but for your kind information i know more than basics may be not good as you but i am ok. i did tried putting dollar sign before but its not working. i think if you try doing on a spreadsheet than may be you know what i am trying to say. the formula you told me above is still not working I've tested and it seems to work for me, but without your exact spreadsheet i can't say for sure what's happening at your end. Broken down this is what the formula is doing (so you might spot what isn't working) =IF(AND(VLOOKUP($G$1,$C$2:$D$11,1,FALSE)=$G$1, find the value in g1 somewhere in the range c2:d11 VLOOKUP($F$2,$A$2:$B$11,1,FALSE)=$F$2) now find the value in f2 somewhere in the range c2:d11 VLOOKUP($F$2,$A$2:$B$11,2,FALSE),0) if both found then find the value of f2 somewhere in the range a2:b11 and return the value 1 column to the right of where it is found I think the problem may lie in the fact that you don't want to search for what is in F2 every time. If so post back and we can adjust. > I've tested and it seems to work for me, but without your exact > spreadsheet i can't say for sure what's happening at your end. [quoted text clipped - 14 lines] > I think the problem may lie in the fact that you don't want to search > for what is in F2 every time. If so post back and we can adjust. Please see the link below where i uploaded my file and explained everything which will be easy for you to understand http://www.savefile.com/files/1501428 OK I've had a look. I think you want on every row to look for G1 in column C and look for F(row number) in column B and if both found on the same row return the value in column B. If this is what you need the formula below works. It only returns a value in your spreadsheet in cell G2 as no other rows have both K12 and the valuein column F found in columns A & C. =IF(C2=$G$1,IF(A2=F2,B2,0),0) Hopefully this is what you need now. > OK I've had a look. > I think you want on every row to look for G1 in column C and look for [quoted text clipped - 7 lines] > > Hopefully this is what you need now. your fromula works fine if i have both table in one sheet but think if i have two sheets in sheet 1 i have table which was in col A to C in other words in which value need to be lookup and sheet 2 where we need to put formula then how you'll make your formula. for example if you check my spreadsheet think that the table which was in col A to C is in sheet 1 and table in col F to G is in sheet 2 where we need to put formula then how you'll write your fomula. sorry to be pain =IF(C2=$G$1,IF(A2=F2,B2,0),0) any cell referred to in this formula is on the sheet where the formula is placed. if you want to refer to cells on other sheets you need to tell the formula which sheet you are referring to; example; =IF(Sheet1!C2=sheet1!$G$1, IF(Sheet2!A2=Sheet2!F2,Sheet1!B2,0),0) You will need to work out which cells are on which sheet and change the sheet names accordingly. Remember to use ! after the sheet name. > =IF(C2=$G$1,IF(A2=F2,B2,0),0) > [quoted text clipped - 10 lines] > You will need to work out which cells are on which sheet and change > the sheet names accordingly. Remember to use ! after the sheet name. Thanks anon and sorry to bother you too much |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.