Lookup Data in Several Columns
 

What formula would I write to lookup a data match from another file that
may be in several columns? Example:

File #A - Contains "RF01" in cell F6
File #B - Contains columns B - G with possible matches but RF01 is in
column C.

=VLOOKUP(F6,STATUS, 2, FALSE)
How do I write a formula to search for my data in columns 1-5 and if it
exists, return a value of "I" instead having to search the exact column?
or could I nest several of the vlookup formulas together for columns B
- G? Any help is greatly appreciated.
Thanks
Cindy


--
simplywitt
------------------------------------------------------------------------
simplywitt's Profile: http://www.excelforum.com/member.php...o&userid=16812
View this thread: http://www.excelforum.com/showthread...hreadid=320104

Hi
and what is your expected result if you found this match for example in
row 3. Which column value should then be returned?

--
Regards
Frank Kabel
Frankfurt, Germany

"simplywitt" schrieb im
Newsbeitrag ...

What formula would I write to lookup a data match from another file
that
may be in several columns? Example:

File #A - Contains "RF01" in cell F6
File #B - Contains columns B - G with possible matches but RF01 is in
column C.

=VLOOKUP(F6,STATUS, 2, FALSE)
How do I write a formula to search for my data in columns 1-5 and if
it
exists, return a value of "I" instead having to search the exact
column?
or could I nest several of the vlookup formulas together for columns
B
- G? Any help is greatly appreciated.
Thanks
Cindy


--
simplywitt
---------------------------------------------------------------------
---
simplywitt's Profile:
http://www.excelforum.com/member.php...o&userid=16812
View this thread:
http://www.excelforum.com/showthread...hreadid=320104

=IF(COUNTIF([B.xls]Sheet1!$B$1:$G$100,F6),1,0)

Hope this helps!

simplywitt Wrote:
What formula would I write to lookup a data match from another file that
may be in several columns? Example:

File #A - Contains "RF01" in cell F6
File #B - Contains columns B - G with possible matches but RF01 is in
column C.

=VLOOKUP(F6,STATUS, 2, FALSE)
How do I write a formula to search for my data in columns 1-5 and if it
exists, return a value of "I" instead having to search the exact column?
or could I nest several of the vlookup formulas together for columns B
- G? Any help is greatly appreciated.
Thanks
Cindy


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=320104

Thanks for your reply however that did not work. I am probably not
explaining myself very well. I wand to write a forumula in spreadsheet
A for cell A3 - I want to lookup value RF05 in spreadsheet B in columns
B - G, if value RF05 exists then I would like to return the value of
"I" if it is found. If it isn't found, return a value of "A" - IS THIS
POSSIBLE?

Spreadsheet A: "RF04" in cell F3
A B C D E F G H
1
2
3 RF05
4
5
6

Spreadsheet B:
A B C D E F G H
1 RF01 RFG3 ST10 SY69 RF05 4G01
2
3
4
5
6


--
simplywitt
------------------------------------------------------------------------
simplywitt's Profile: http://www.excelforum.com/member.php...o&userid=16812
View this thread: http://www.excelforum.com/showthread...hreadid=320104

Try the following...

=IF(COUNTIF(SheetB!B1:G100,SheetA!F3),"I","A")

...where SheetA!F3 contains your look up value (you can change that to
which ever cell suits) and Sheet1B!B1:G100 contains your range of data
for your lookup table (change this range accordingly).

Hope this helps!

simplywitt Wrote:
Thanks for your reply however that did not work. I am probably not
explaining myself very well. I wand to write a forumula in spreadsheet
A for cell A3 - I want to lookup value RF05 in spreadsheet B in columns
B - G, if value RF05 exists then I would like to return the value of
"I" if it is found. If it isn't found, return a value of "A" - IS THIS
POSSIBLE?

Spreadsheet A: "RF04" in cell F3
A B C D E F G H
1
2
3 RF05
4
5
6

Spreadsheet B:
A B C D E F G H
1 RF01 RFG3 ST10 SY69 RF05 4G01
2
3
4
5
6


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=320104