Lookup Data in Several Columns

[simplywitt]

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

 

[Frank Kabel]

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

 

[Domenic]

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

Hope this helps!

 

[simplywitt]

Thanks for your reply however that did not work. I am probably no
explaining myself very well. I wand to write a forumula in spreadshee
A for cell A3 - I want to lookup value RF05 in spreadsheet B in column
B - G, if value RF05 exists then I would like to return the value o
"I" if it is found. If it isn't found, return a value of "A" - IS THI
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

 

[Domenic]

Try the following...

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

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

Hope this helps!