COMPLICATED VLOOKUP

[shaqil]

Dear All,

I have the data in following format:

Invoice # Chq # Amount Paid
1 10
2 20
3 30
1,3 987 40

I want to lookup the invoice # which has been paid thru chq and mark
"Y" in Paid column. In above example invoice # 1 and 3 has been paid
thru Chq # 987 so they should be marked "Y".

Can anybody help me?

Thnx in advance.

 

[Joel]

The simpliest approach is to put a simple equation into the paid column

for row 1
=if(counta(B1)>1,"Y","N")
where column B is the Chq # column. Copy the formula down all rows in the
Paid column.

 

[Tom Ogilvy]

in the paid column (assume that is D)

d2: =if(countif(A:A,"*"&A2&"*")>1,"Y","N")

This assumes that your invoice numbers are unique. In otherwords if your
invoice numbers were

1
5
10
5,10

then the formula in D2 would match the 1 found in the string in 5,10 to the
first invoice number (1) which would be incorrect. This isn't a problem if
you have invoice numbers like

111
112
113
114
112,114

where one invoice number is not a subset of another invoice number.

 

[Joel]

Tom it should either be >0 or >=1

in the paid column (assume that is D)

d2: =if(countif(A:A,"*"&A2&"*")>1,"Y","N")

This assumes that your invoice numbers are unique. In otherwords if your
invoice numbers were

1
5
10
5,10

then the formula in D2 would match the 1 found in the string in 5,10 to the
first invoice number (1) which would be incorrect. This isn't a problem if
you have invoice numbers like

111
112
113
114
112,114

where one invoice number is not a subset of another invoice number.

 

[Tom Ogilvy]

It should be as I wrote it. It will count the original invoice, so it should
always be at least 1.

 

[Joel]

Tom: >1 doesn't include 1.

It should be as I wrote it. It will count the original invoice, so it should
always be at least 1.

 

[Tom Ogilvy]

I see the confusion Joel and also see I didn't really explain this completely
or well.

If all of column A values are stored as text values, then the formula as
written works fine. (and as I envisioned.) If the invoice numbers are
alpha or alpha numberic and stored as text or numberic and stored as text
then as in the example below

A111
A112
A113
A111, A113

then it works as written. =IF(COUNTIF(A:A,"*"&A2&"*")>1,"Y","N")

If the invoice numbers are numeric and stored as numbers

111
112
113
111,113

then the invoice number itself will not be counted and you are correct that
the formula should use >0.
=IF(COUNTIF(A:A,"*"&A2&"*")>0,"Y","N")

However, in this latter case, if only a single invoice is paid with a check,
then this will be problematic again if that invoice number is stored as
numeric. My advice would be to format Column A as Text and use the original
formula.

Does that clear it up. (thanks for pointing this out).

--
regards,
Tom Ogilvy

 

[Joel]

I don't know if the invoice column is really needed in the formula. I'm not
sure what Lookup means in original posting. Neither Tom's approach or my
approach actually performs a LOOKUP! If we are not doing a lookup then why
look at column A when just having a check number implies that the item has
been paid????? Column A has no value in the process.

 

[shaqil]

I see the confusion Joel and also see I didn't really explain this completely
or well.

If all of column A values are stored as text values, then the formula as
written works fine. (and as I envisioned.) If the invoice numbers are
alpha or alpha numberic and stored as text or numberic and stored as text
then as in the example below

A111
A112
A113
A111, A113

then it works as written. =IF(COUNTIF(A:A,"*"&A2&"*")>1,"Y","N")

If the invoice numbers are numeric and stored as numbers

111
112
113
111,113

then the invoice number itself will not be counted and you are correct that
the formula should use >0.
=IF(COUNTIF(A:A,"*"&A2&"*")>0,"Y","N")

However, in this latter case, if only a single invoice is paid with a check,
then this will be problematic again if that invoice number is stored as
numeric. My advice would be to format Column A as Text and use the original
formula.

Does that clear it up. (thanks for pointing this out).

--
regards,
Tom Ogilvy

--
Regards,
Tom Ogilvy






- Show quoted text -

Thanks a lot, I really want exactly as u explained.