If else when formula

  • Thread starter Thread starter problem in matching DB formula
  • Start date Start date
P

problem in matching DB formula

hi,

how if i want to compare a data entered by user in a column if it is matched
with the DB created in excel?

eg, if user key in 'A' in column G4, column H4 should display whichever data
that matches the user key in 'A' from DB (column C) , all the matched 'A'
alpabet from the DB column C will display in column H4.

Thanks.

regards,
moon
 
If I understand correctly, you want to enter a value in G4. Everything that
matches this in column C should show in column H. Everything else in column
H should be blank. If so, enter this in H4 then use fill fill handle to drag
it down the range in column H you need:

=IF(C4=$G$4,C4,"")

--
Steve

"problem in matching DB formula" <problem in matching DB
(e-mail address removed)> wrote in message
news:[email protected]...
 
Excel has some lookup function that do what you want but, my favoured method
is to use a combination of functions - the INDEX and MATCH method.

Copy these to indicated cells (without quotes) and drag down:

H4 "=INDEX(A:E,MATCH(G4,B:B,0),3)"
I4 "=INDEX(A:E,MATCH(G4,B:B,0),4)


In summary, MATCH() returns the row in the table: INDEX() returns the value
from your table when given the row and column.

Here is some information on using the functions:
http://www.ozgrid.com/Excel/left-lookup.htm
http://www.contextures.com/xlFunctions03.html

The latter site breaks it down well.

It is well worth the time invested to learn how to use Index & Match if you
regularly need to look up information in a table.
 
Steve,

Thanks you very much..:)
somehow, it does not work..:(

many thanks,
Ling
 
If the value returned is #N/A is is telling you it found no postcode that
matched G4 in column B. If this is not the problem read on.

Your question was well worded in respect of data layout and your
requirements (well done). So, unless there is something else I don't
understand I believe I know what you need. I always test before sending
someone a formula so am confident it worked at this end. Did you remove the
quote marks? For example, cell H4 should contain the formula exactly as
follows:

=INDEX(A:E,MATCH(G4,B:B,0),3)


The above formula is saying:

1) MATCH part
MATCH(G4,B:B,0) :

- Look at the value in G4 (your required postcode)
- Find it in column B (your lookup table postcode column)
- Find the first value that is an exact match

The MATCH function returns the row in which it found the value or #N/A if it
cannot find an exact match.

B) INDEX
INDEX(A:E,MATCH(G4,B:B,0),3)

Looking at what MATCH did
INDEX(A:E,{The row number from the MATCH function},3)

Let's assume the MATCH function returned 100
At H4 this translates to
INDEX(A:E, 100,3)

So INDEX us saying
- From columns A to E inclusive
return the value that is found
- One-hundred rows down from the top left-most cell
- Three rows across from the to left-most cell

NOTE: in formula above commas separate formula parameters. Each dash above
explains the different parameters in (I hope) simple terms.

If you still cannot get it to work, reply with a munged email address and I
will contact you direct to work through the problem.
 
Back
Top