Vook up help required

[Ali Noor]

Dear All,

Facing problem to write a proper lookup formula for following problem,

Look value in Coulmn A match it in Coulmn B and return the Number In coulmn C

where i can not array B and C and need C coulmn value as under


Column 1 Column 2 Column3 Column4

1234 1234 15 Require V look up to give 15 as
answer.


Hope to hear form all the experts soon

Thx in advance

Regards......Ali

 

[John C]

Assuming you will only have single instances for A&B, and values always in C,
you could use SUMPRODUCT:
=SUMPRODUCT(--(A2:A100=1234),--(B2:B100=1234),(C2:C100))

Hope this helps.

 

[Ali Noor]

Dear John,

I have applied the formula s below thoug my values were in A1 B1 AND C1 so i
changed it how ever in D column it says 0 rather than 15.

Moreover to look up A1 values in important in B1 as B1 is usualy very long
list and A1 also is long list many a times A1 value might not be in B1

 

[John C]

Your ranges must all be the same length. If your values start in row 1, then
modify the formula as needed. My understanding is you want the numeric value
of 15 (ensure that it is numeric, and not text) from cell C1.

D1: =SUMPRODUCT(--(A1:A1000=1234),--(B1:B1000=5678),(C1:C1000))
This will check column A, rows 1 through 1000, to see if what is in that
column is equal to 1234 (or whatever cell reference you are using), then do
the same for column B, rows 1 through 1000, to see if it is equal to 5678 (or
whatever cell reference you are using). It will then find the value in column
C, and return that value.
NOTE: If you have multiple matches...
A3: =1234 B3: =5678 C3=15
....
A9: =1234 B3: =5678 C3=25
It will add up the values in column C (this case, 40)

 

[Ali Noor]

Dear John,

Let me put the original prbolem as under,

1540268 10526 0
1540269 7210 0 #REF!
1540270 7211 5604
1540650 7640 1449255
1542686 9985 1449256
1542687 10531 1464772
1542688 10382 1555179
1542689 8785 1579721
1542690 8728 151157
1542691 9019 1513969
1543953 9016 1514582
1543954 9350 1520873
1543955 9534 1522033
1543956 9567 1529466
1545267 9568 1540173
1545268 9569 1540268
1545269 9570 1540269
1545270 9615 1540270
1545271 9616 1540650


The coulmn 1 Invoice Numbers has to be macthed with All the Invice Numbers
in 3ed Coulmn and if found then return the corresponding value of 2nd column
for 3rd.

Note Second and third Coulmns i can not array as the Second coulmn

So now if i see 1540268 in Last Column its is there and the corresponding
value is 9569 in the Second Column.

Hope you can help

 

[John C]

Ok, that's not how your original question was posted. I will first describe
what I think you are asking, and then give you the formula.
You have ALL invoice numbers in column A, you have some value in column B,
then you have the invoice numbers again (in a different order) in column C.
Then you want to find the same invoice that matches your invoice in column C,
and find it in column A, and give the value in column B that is next to the
invoice in column A (I hope you understand that, lol).

Assuming, obviously, that column A invoice numbers are ALL unique numbers
for invoices:
D1: =IF(COUNTIF($A$1:$A$1000,C1)=0,"No
Match",VLOOKUP(C1,$A$1:$B$1000,2,FALSE))

 

[Ali Noor]

Dear John,

I realy appricate your concern can u please give me your e-mail address want
to attach Excel sheet.

This might give you some clear idea.....and some more help for me

Thanks a lot again for your concern.

 

[John C]

Sorry, I don't post my e-mail in use groups (tends to get slammed with spam).
There are a number of sites that if you search for, you can 'post your file
online', I can't think of any off the top of my head, but if you do a search,
you will find it. Post it, and I will take a look at it for you.

 

[Ali Noor]

Dear John,

I realy realy am thanful for ur extended support and guess what i stumbled
on the formula my self........was making mistake in selecting TABLE
ARRAY.....got it ok now.....how ever may you be blessed to do more and more
good,

Kind Regards,

 

[John C]

Glad you were able to get it
--
John C

Dear John,

I realy realy am thanful for ur extended support and guess what i stumbled
on the formula my self........was making mistake in selecting TABLE
ARRAY.....got it ok now.....how ever may you be blessed to do more and more
good,

Kind Regards,