VLOOKUP OR MATCH FORMULA

[K]

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.

 

[anon]

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.

 

[K]

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.

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.

 

[anon]

OK got you.

=IF(AND(VLOOKUP(G1,C211,1,FALSE)=G1,VLOOKUP(F2,A2:B11,1,FALSE)=F2),VLOOKUP(F2,A2:B11,2,FALSE),
0)

 

[K]

OK got you.

=IF(AND(VLOOKUP(G1,C211,1,FALSE)=G1,VLOOKUP(F2,A2:B11,1,FALSE)=F2),VLOOKU­P(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

 

[anon]

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),VLOOKU­­P($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.

 

[K]

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),VLOOKU­­P($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.

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

 

[anon]

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

VLOOKU­­P($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.

 

[K]

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

VLOOKU­­P($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.

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

 

[anon]

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.

 

[K]

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.

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

 

[anon]

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

 

[K]

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

Thanks anon and sorry to bother you too much