Vlookup help please!

  • Thread starter Thread starter TaGY2K
  • Start date Start date
T

TaGY2K

Ok.. I have one spreadsheet.. with 2 sheet..

Say .. Colum A and B on sheet 1 and A on sheet 2
... what I want is ...

Sheet 1

A B
Joe 10

and sheet 2

A B

Joe

basically .. what I want is if sheet1 A matches to Sheet2 A and take
the value of sheet 1 B and put it in sheet2 B


Thanks

TaGY2K
 
Ok.. I have one spreadsheet.. with 2 sheet..

Say .. Colum A and B on sheet 1 and A on sheet 2
.. what I want is ...

Sheet 1

A B
Joe 10

and sheet 2

A B

Joe

basically .. what I want is if sheet1 A matches to Sheet2 A and take
the value of sheet 1 B and put it in sheet2 B
Click to expand...

=IF(A2=Sheet1!A2,Sheet1!B2,"no match")
 
put this formula in Sheet2 Cell B1 and copy down. Whenever possible, use
index/match over vlookup... much more powerful/dynamic.

=INDEX(Sheet1!A:B,MATCH(A1,Sheet1!A:A,0),2)

Hope this helps,

ryanb.
 
I forgot to error proof the last formula... use this instead:

=IF(ISNA(INDEX(Sheet1!A:B,MATCH(A1,Sheet1!A:A,0),2)),"",INDEX(Sheet1!A:B,MAT
CH(A1,Sheet1!A:A,0),2))

no N/A#! values then

ryanb.
 
Could you explain to me in plain english what the formula means..

=INDEX(Sheet1!A:B,MATCH(A1,Sheet1!A:A,0),2)

Thanks..
 
This can be made a bit more efficient:

=IF(ISNA(MATCH(A1, Sheet1!A:A, FALSE)), "", VLOOKUP(A1, Sheet1!A:B,
2, FALSE))

ryanb. said:
I forgot to error proof the last formula... use this instead:

=IF(ISNA(INDEX(Sheet1!A:B,MATCH(A1,Sheet1!A:A,0),2)),"",INDEX(Sheet1!A:B,MAT
CH(A1,Sheet1!A:A,0),2))

no N/A#! values then

ryanb.
Click to expand...
 
No problem: I will do my best

It is a MATCH formula nested in an INDEX formula

where INDEX(array,row,column)
- array is columns A & B on sheet 1 (your range)
- the MATCH function is used as a dynamic means of finding your row #
- the column is 2 columns over.
- think of it as coordinates when plying battleship only you do not
specifically know what row you need, so you use the match formula to find it
(if it exists).

where MATCH (reference,array,type)
- reference is the value you are looking for... in this example.. cell A1
- array is the range you want to search for the reference in this case
column A
- type is a match type: 0=Exact (what we used)... other types are defined in
your excel help

In the formula I sent you, the MATCH formula was used as the ROW in the
INDEX formula. That way, no matter what row the reference you are looking
for is in (in column A), MATCH will find it.

If there is not a match, then INDEX will return and NA#! value. The second
formula I posted takes care of that by using an IF statement and an "anti"
NA#! value function (ISNA). If you need this explained as well, let me
know. it is nice to not have NA#! show up because they can screw up SUMs
and other formulas. I basically told Excel that is the cell returns a value
of NA#!, make the cell blank (""), and if not, perform the INDEX/MATCH
formula.

Does that make sense? If not let me know.

ryanb.
 
More concise, yes, but more efficient, not in my book. A vlookup is limited
to moving one direction (right) within a range while INDEX/MATCH can move
all over the range. Flexibility is efficiency in my book, and that makes
INDEX/MATCH my formula of choice over vlookup/hlookup. Plus for newbies it
is much easier when they can supplant all of the vlookup and hlookup
formulas with one formula... INDEX/MATCH.

Thanks,

ryanb.

J.E. McGimpsey said:
This can be made a bit more efficient:

=IF(ISNA(MATCH(A1, Sheet1!A:A, FALSE)), "", VLOOKUP(A1, Sheet1!A:B,
2, FALSE))
Click to expand...
 
Efficient in the sense of not calling unnecessary functions [i.e.,
ISNA(MATCH()) instead of your ISNA(INDEX(MATCH()))], and in
VLOOKUP's performance advantage over INDEX(MATCH()).

I suspect your book is rather ideosyncratic - in my experience
flexibility and efficiency are more often seen as separate, often
competing, goals than they are as identities. I also have seen more
newbie eyes glaze over at nested functions than a simple VLOOKUP(),
but everyone's experience is different.

There's also a good design reason that VLOOKUP is a separate
optimized function - effective design tends to go from left to
right, at least for English speakers. Probably 98% of the lookup
tables I've ever seen are set up that way.
 
I don't think J.E. necessarily meant vlookup visavi index & match although I
am sure vlookup is faster. What he probably meant is that you use the whole
formula for error trapping i.e.

=IF(ISNA(INDEX(Sheet1!A:B,MATCH(A1,Sheet1!A:A,0),2)),"",

as opposed to

=IF(ISNA(MATCH(A1, Sheet1!A:A, FALSE)), "",
 
Yes, I see what he is saying, I included an extra function because I simply
copy/pasted the formula quickly. My whole contention is, unlike in a
perfect world where a data chunk is set up the way you need it (left to
right), end user needs and data changes. End users' needs change all of the
time or you may not even have control over how a chunk of data is set up.
Thus... possibly having to re-organize the data for a major change or having
to do it because of an ERP system's export limitations becomes very time
consuming. Sure you can use VBA to get around that, but using Index/match
allows me the flexibility of not reorganizing data, but merely adjusting the
column or row number because it is more flexible/dynamic... time-saving for
me, lower maintenance. It is slower than a vlookup, but it would need to be
a large workbook to notice it... at least larger than the one described in
the post I responded to.

ryanb.
 
hi ryanb
I am using the formula your this formula
=INDEX(BankAccount!F:F,MATCH(E42,BankAccount!F:F,0),0)
it works fine for me but as I copied it down so when there is no value to
look for(ie in E42) it returns the #N/A.

how can I get rid of it. I know you mentioned ISNA in you mail but could you
please explain it here for this formula.
thanks
ryanb. said:
put this formula in Sheet2 Cell B1 and copy down. Whenever possible, use
index/match over vlookup... much more powerful/dynamic.

=INDEX(Sheet1!A:B,MATCH(A1,Sheet1!A:A,0),2)

Hope this helps,

ryanb.
Click to expand...
 
use the following:

=IF(ISNA(MATCH(E42,BankAccount!F:F,0)),"",INDEX(BankAccount!F:F,MATCH(E42,Ba
nkAccount!F:F,0),0))

using "" will put any cell without a match as blank. if you want the
un-matched cells to be 0, use 0 in place of "".

the ISNA formula returns true or false based on the function called (NA
cells=True). The above formula says, if ISNA returns a true (if the cell
has no match), then make the cell blank (""), if false (there is a match)...
perform the index/match function. Good luck

HTH,

ryanb.



MeAgain said:
hi ryanb
I am using the formula your this formula
=INDEX(BankAccount!F:F,MATCH(E42,BankAccount!F:F,0),0)
it works fine for me but as I copied it down so when there is no value to
look for(ie in E42) it returns the #N/A.

how can I get rid of it. I know you mentioned ISNA in you mail but could you
please explain it here for this formula.
thanks
Click to expand...
 
Since you're just returning the same value you're looking for (by
using Column F in both the Index and the Match, you could also use:

=IF(COUNTIF(BankAccount!F:F,E42),E42,"")

or

=IF(ISNA(MATCH(E42,BankAccount!F:F,FALSE)),"",E42)
 
thanks dude it sorted.
There is another one.
here is the scenario.
sheet TAccount

TESCO
Date Dr Cr
22-10-03 (Formula here)
27-10-03 (Formula here)

SHEET Purchases

Date Supplier Total
22-10-03 TESCO 1503.49
23-10-03 ASDA 230.23
24-10-03 etc 23.20
27-10-03 TESCO 990.38

I need a formula to return the value for Dr column in TAccount sheet from
the purchases sheet like IF there is TESCO and date 22-10-03 in the same row
get Total from column 3.
so the result would be 1503.49 ELSE 0 and so on
thanks
 
well its even more simple.
I have changed the last bit E42 to 0
There is another one.
here is the scenario.
sheet TAccount

TESCO
Date Dr Cr
22-10-03 (Formula here)
27-10-03 (Formula here)

SHEET Purchases

Date Supplier Total
22-10-03 TESCO 1503.49
23-10-03 ASDA 230.23
24-10-03 etc 23.20
27-10-03 TESCO 990.38

I need a formula to return the value for Dr column in TAccount sheet from
the purchases sheet like IF there is TESCO and date 22-10-03 in the same row
get Total from column 3.
so the result would be 1503.49 ELSE 0 and so on
thanks
 
See my reply using SUMPRODUCT() in the "hiding #N/A" thread

Please don't start another thread. Replying to previous posts keeps
answers tied together.
 
Definitely use the sumproduct formula JE provided, much easier than an
array. If it is possible to have more than one transaction from the same
vendor on the same date? If yes, I would suggest inserting a # column
between Date and DR with a count of transactions so that if you are ever
reconciling your entry you will know that the amount is more than one
transaction (as sumproduct will sum them). If not nothing to worry about.

ryanb.
 
Back
Top