Vlookup for more than one cell
 

Worksheet #1 has a lot of data Within the data we have the Word "LIAB" in
column A and # in Column B See Below
A B C
Aug-05 1103 44,023.151
Jul-05 1103 42,684.574
LIAB 1103 340,573.884
AGED 1103 1,235,569.497
TOTALS 1103 1,576,143.381
Aug-05 1265 365.497
Jul-05 1265 7,012.625
LIAB 1265 64,477.315
AGED 1265 174,469.632
TOTALS 1265 238,946.947


My new spreadsheet #@ has just numbers.

A B
1103
1105
1106

How do I do a VLOOK UP to say: look for the numberA1from Sheet #2 in Sheet
#1 and if it has the word LIAB in Column A then take the corresponding cell
value in sheet #1 column C and put it into column B of Sheet #2

--
Thanks
ETC

Vlookup for more than one cell
 

You could try this *array* formula in B1 of Sheet2:

=INDEX(Sheet1!$C$1:$C$10,MATCH(1,(Sheet1!$B$1:$B$1 0=A1)*(Sheet1!$A$1:$A$10="
LIAB"),0))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

After the CSE entry, copy down as needed.

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Sweetetc" wrote in message
...
Worksheet #1 has a lot of data Within the data we have the Word "LIAB" in
column A and # in Column B See Below
A B C
Aug-05 1103 44,023.151
Jul-05 1103 42,684.574
LIAB 1103 340,573.884
AGED 1103 1,235,569.497
TOTALS 1103 1,576,143.381
Aug-05 1265 365.497
Jul-05 1265 7,012.625
LIAB 1265 64,477.315
AGED 1265 174,469.632
TOTALS 1265 238,946.947


My new spreadsheet #@ has just numbers.

A B
1103
1105
1106

How do I do a VLOOK UP to say: look for the numberA1from Sheet #2 in Sheet
#1 and if it has the word LIAB in Column A then take the corresponding cell
value in sheet #1 column C and put it into column B of Sheet #2

--
Thanks
ETC

Vlookup for more than one cell
 

Ray thanks for your quick response. I am not familiar with arrays, but sheet
1 has hundreds of lines of data and it appears your formula is calling for
absolute values (if I am reading it right) That is why I was hoping that I
could do look up that says vlookup.
--
Thanks
ETC


"Sweetetc" wrote:

Worksheet #1 has a lot of data Within the data we have the Word "LIAB" in
column A and # in Column B See Below
A B C
Aug-05 1103 44,023.151
Jul-05 1103 42,684.574
LIAB 1103 340,573.884
AGED 1103 1,235,569.497
TOTALS 1103 1,576,143.381
Aug-05 1265 365.497
Jul-05 1265 7,012.625
LIAB 1265 64,477.315
AGED 1265 174,469.632
TOTALS 1265 238,946.947


My new spreadsheet #@ has just numbers.

A B
1103
1105
1106

How do I do a VLOOK UP to say: look for the numberA1from Sheet #2 in Sheet
#1 and if it has the word LIAB in Column A then take the corresponding cell
value in sheet #1 column C and put it into column B of Sheet #2

--
Thanks
ETC

Vlookup for more than one cell
 

hi!

why don't you try

=index(sheet2!A1:A10,match(c1,sheet2!B1:B10,0))

where c1 houses your lookup value?

-via135


Sweetetc Wrote:
Worksheet #1 has a lot of data Within the data we have the Word "LIAB"
in
column A and # in Column B See Below
A B C
Aug-05 1103 44,023.151
Jul-05 1103 42,684.574
LIAB 1103 340,573.884
AGED 1103 1,235,569.497
TOTALS 1103 1,576,143.381
Aug-05 1265 365.497
Jul-05 1265 7,012.625
LIAB 1265 64,477.315
AGED 1265 174,469.632
TOTALS 1265 238,946.947


My new spreadsheet #@ has just numbers.

A B
1103
1105
1106

How do I do a VLOOK UP to say: look for the numberA1from Sheet #2 in
Sheet
#1 and if it has the word LIAB in Column A then take the corresponding
cell
value in sheet #1 column C and put it into column B of Sheet #2

--
Thanks
ETC


--
via135
------------------------------------------------------------------------
via135's Profile: http://www.excelforum.com/member.php...o&userid=26725
View this thread: http://www.excelforum.com/showthread...hreadid=564006

Vlookup for more than one cell
 

I was hoping that I could do look up that says vlookup.

Vlookup won't work in this case.

An alternative to the array formula**:

=SUMPRODUCT(--(Sheet1!A$1:A$100="LIAB"),--(Sheet1!B$1:B$100=1103),Sheet1!C$1:C$100)

** This will only return the correct result if there is only one instance of
LIAB 1003.

Biff

"Sweetetc" wrote in message
...
Worksheet #1 has a lot of data Within the data we have the Word "LIAB"
in
column A and # in Column B See Below
A B C
Aug-05 1103 44,023.151
Jul-05 1103 42,684.574
LIAB 1103 340,573.884
AGED 1103 1,235,569.497
TOTALS 1103 1,576,143.381
Aug-05 1265 365.497
Jul-05 1265 7,012.625
LIAB 1265 64,477.315
AGED 1265 174,469.632
TOTALS 1265 238,946.947


My new spreadsheet #@ has just numbers.

A B
1103
1105
1106

How do I do a VLOOK UP to say: look for the numberA1from Sheet #2 in
Sheet
#1 and if it has the word LIAB in Column A then take the corresponding
cell
value in sheet #1 column C and put it into column B of Sheet #2

--
Thanks
ETC

Vlookup for more than one cell
 

Even if I put the look up value in c1 Sheet 2 as LIAB 1103
Sheet #1 houses them in 2 different cells. How does this lookup work.
--
Thanks
ETC


"via135" wrote:


hi!

why don't you try

=index(sheet2!A1:A10,match(c1,sheet2!B1:B10,0))

where c1 houses your lookup value?

-via135


Sweetetc Wrote:
Worksheet #1 has a lot of data Within the data we have the Word "LIAB"
in
column A and # in Column B See Below
A B C
Aug-05 1103 44,023.151
Jul-05 1103 42,684.574
LIAB 1103 340,573.884
AGED 1103 1,235,569.497
TOTALS 1103 1,576,143.381
Aug-05 1265 365.497
Jul-05 1265 7,012.625
LIAB 1265 64,477.315
AGED 1265 174,469.632
TOTALS 1265 238,946.947


My new spreadsheet #@ has just numbers.

A B
1103
1105
1106

How do I do a VLOOK UP to say: look for the numberA1from Sheet #2 in
Sheet
#1 and if it has the word LIAB in Column A then take the corresponding
cell
value in sheet #1 column C and put it into column B of Sheet #2

--
Thanks
ETC


--
via135
------------------------------------------------------------------------
via135's Profile: http://www.excelforum.com/member.php...o&userid=26725
View this thread: http://www.excelforum.com/showthread...hreadid=564006

Vlookup for more than one cell
 

yes..Biff is correct!

-via135


Sweetetc Wrote:
Even if I put the look up value in c1 Sheet 2 as LIAB 1103
Sheet #1 houses them in 2 different cells. How does this lookup work.
--
Thanks
ETC


"via135" wrote:


hi!

why don't you try

=index(sheet2!A1:A10,match(c1,sheet2!B1:B10,0))

where c1 houses your lookup value?

-via135


Sweetetc Wrote:
Worksheet #1 has a lot of data Within the data we have the Word
"LIAB"
in
column A and # in Column B See Below
A B C
Aug-05 1103 44,023.151
Jul-05 1103 42,684.574
LIAB 1103 340,573.884
AGED 1103 1,235,569.497
TOTALS 1103 1,576,143.381
Aug-05 1265 365.497
Jul-05 1265 7,012.625
LIAB 1265 64,477.315
AGED 1265 174,469.632
TOTALS 1265 238,946.947


My new spreadsheet #@ has just numbers.

A B
1103
1105
1106

How do I do a VLOOK UP to say: look for the numberA1from Sheet #2
in
Sheet
#1 and if it has the word LIAB in Column A then take the
corresponding
cell
value in sheet #1 column C and put it into column B of Sheet #2

--
Thanks
ETC


--
via135

------------------------------------------------------------------------
via135's Profile:
http://www.excelforum.com/member.php...o&userid=26725
View this thread:
http://www.excelforum.com/showthread...hreadid=564006




--
via135
------------------------------------------------------------------------
via135's Profile: http://www.excelforum.com/member.php...o&userid=26725
View this thread: http://www.excelforum.com/showthread...hreadid=564006

Vlookup for more than one cell
 

Let me give it a try

Thanks
ETC


"Biff" wrote:

I was hoping that I could do look up that says vlookup.

Vlookup won't work in this case.

An alternative to the array formula**:

=SUMPRODUCT(--(Sheet1!A$1:A$100="LIAB"),--(Sheet1!B$1:B$100=1103),Sheet1!C$1:C$100)

** This will only return the correct result if there is only one instance of
LIAB 1003.

Biff

"Sweetetc" wrote in message
...
Worksheet #1 has a lot of data Within the data we have the Word "LIAB"
in
column A and # in Column B See Below
A B C
Aug-05 1103 44,023.151
Jul-05 1103 42,684.574
LIAB 1103 340,573.884
AGED 1103 1,235,569.497
TOTALS 1103 1,576,143.381
Aug-05 1265 365.497
Jul-05 1265 7,012.625
LIAB 1265 64,477.315
AGED 1265 174,469.632
TOTALS 1265 238,946.947


My new spreadsheet #@ has just numbers.

A B
1103
1105
1106

How do I do a VLOOK UP to say: look for the numberA1from Sheet #2 in
Sheet
#1 and if it has the word LIAB in Column A then take the corresponding
cell
value in sheet #1 column C and put it into column B of Sheet #2

--
Thanks
ETC